Fixing Excel timesheet by time in and time out per user and date

up vote
0
down vote

favorite

I have this:

badly formatted table

which is difficult to read, 'I' means time in and 'O' means time out.

I want to transform it like this:

this

I want to align each time in and time out to its respective employee ID and date. Thanks

EDIT

I's and O's in the first table aren't necessarily alternating, there may be 3 I's before its respective O show up.

The result should be:
1. missing values of time in and out should show as blank
2. editable so the user can fill out missing time ins or outs and;
3. the t

edited 2 days ago

asked Nov 19 at 3:16

NoobPro

New contributor

Possible duplicate of How to combine values from multiple rows into a single row in Excel?
– Scott
Nov 19 at 3:27

Create a pivot, use employee number and date as the rows, and min and max of time for time as the values for time in and time
– PeterH
2 days ago

can there be multiple ins on the same date? Conversely multiple outs on the same date?
– Forward Ed
2 days ago

@ForwardEd, yes and there can also be no In time for a certain date but has a OUT time
– NoobPro
23 hours ago

add a comment |

up vote
0
down vote

favorite

I have this:

badly formatted table

which is difficult to read, 'I' means time in and 'O' means time out.

I want to transform it like this:

this

I want to align each time in and time out to its respective employee ID and date. Thanks

EDIT

I's and O's in the first table aren't necessarily alternating, there may be 3 I's before its respective O show up.

The result should be:
1. missing values of time in and out should show as blank
2. editable so the user can fill out missing time ins or outs and;
3. the t

edited 2 days ago

asked Nov 19 at 3:16

NoobPro

New contributor

Possible duplicate of How to combine values from multiple rows into a single row in Excel?
– Scott
Nov 19 at 3:27

Create a pivot, use employee number and date as the rows, and min and max of time for time as the values for time in and time
– PeterH
2 days ago

can there be multiple ins on the same date? Conversely multiple outs on the same date?
– Forward Ed
2 days ago

@ForwardEd, yes and there can also be no In time for a certain date but has a OUT time
– NoobPro
23 hours ago

add a comment |

up vote
0
down vote

favorite

I have this:

badly formatted table

which is difficult to read, 'I' means time in and 'O' means time out.

I want to transform it like this:

this

I want to align each time in and time out to its respective employee ID and date. Thanks

EDIT

I's and O's in the first table aren't necessarily alternating, there may be 3 I's before its respective O show up.

The result should be:
1. missing values of time in and out should show as blank
2. editable so the user can fill out missing time ins or outs and;
3. the t

edited 2 days ago

asked Nov 19 at 3:16

NoobPro

New contributor

I have this:

badly formatted table

which is difficult to read, 'I' means time in and 'O' means time out.

I want to transform it like this:

this

I want to align each time in and time out to its respective employee ID and date. Thanks

EDIT

I's and O's in the first table aren't necessarily alternating, there may be 3 I's before its respective O show up.

The result should be:
1. missing values of time in and out should show as blank
2. editable so the user can fill out missing time ins or outs and;
3. the t

microsoft-excel worksheet-function microsoft-excel-2016

edited 2 days ago

asked Nov 19 at 3:16

NoobPro

New contributor

edited 2 days ago

asked Nov 19 at 3:16

NoobPro

New contributor

edited 2 days ago

asked Nov 19 at 3:16

NoobPro

New contributor

asked Nov 19 at 3:16

NoobPro

asked Nov 19 at 3:16

NoobPro

New contributor

NoobPro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

Possible duplicate of How to combine values from multiple rows into a single row in Excel?
– Scott
Nov 19 at 3:27

Create a pivot, use employee number and date as the rows, and min and max of time for time as the values for time in and time
– PeterH
2 days ago

can there be multiple ins on the same date? Conversely multiple outs on the same date?
– Forward Ed
2 days ago

@ForwardEd, yes and there can also be no In time for a certain date but has a OUT time
– NoobPro
23 hours ago

add a comment |

Possible duplicate of How to combine values from multiple rows into a single row in Excel?
– Scott
Nov 19 at 3:27

Create a pivot, use employee number and date as the rows, and min and max of time for time as the values for time in and time
– PeterH
2 days ago

can there be multiple ins on the same date? Conversely multiple outs on the same date?
– Forward Ed
2 days ago

@ForwardEd, yes and there can also be no In time for a certain date but has a OUT time
– NoobPro
23 hours ago

Possible duplicate of How to combine values from multiple rows into a single row in Excel?
– Scott
Nov 19 at 3:27

Create a pivot, use employee number and date as the rows, and min and max of time for time as the values for time in and time
– PeterH
2 days ago

can there be multiple ins on the same date? Conversely multiple outs on the same date?
– Forward Ed
2 days ago

@ForwardEd, yes and there can also be no In time for a certain date but has a OUT time
– NoobPro
23 hours ago

add a comment |

1 Answer
1

active

oldest

votes

up vote
0
down vote

Create a Pivot Table from your data with the following configuration:

Pivot

You'll probably also need to format the value as a Time:

format

And you may also want to display the Pivot Table in tabular format to yield the desired result:

tabular

Also turning off the display of Subtotals & Grand Totals:

subtotals

grandtotals

Note that if there are multiple In/Out records per day per employee, this will only show the latest In or Out record (by virtue of aggregating with Max).

answered 2 days ago

Lee Mac

426110

Hey it works, but i need the result to be editable. Sorry saying it just now, I've edited my post
– NoobPro
2 days ago

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

NoobPro is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1376589%2ffixing-excel-timesheet-by-time-in-and-time-out-per-user-and-date%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

Create a Pivot Table from your data with the following configuration:

Pivot

You'll probably also need to format the value as a Time:

format

And you may also want to display the Pivot Table in tabular format to yield the desired result:

tabular

Also turning off the display of Subtotals & Grand Totals:

subtotals

grandtotals

Note that if there are multiple In/Out records per day per employee, this will only show the latest In or Out record (by virtue of aggregating with Max).

answered 2 days ago

Lee Mac

426110

Hey it works, but i need the result to be editable. Sorry saying it just now, I've edited my post
– NoobPro
2 days ago

add a comment |

up vote
0
down vote

Create a Pivot Table from your data with the following configuration:

Pivot

You'll probably also need to format the value as a Time:

format

And you may also want to display the Pivot Table in tabular format to yield the desired result:

tabular

Also turning off the display of Subtotals & Grand Totals:

subtotals

grandtotals

Note that if there are multiple In/Out records per day per employee, this will only show the latest In or Out record (by virtue of aggregating with Max).

answered 2 days ago

Lee Mac

426110

Hey it works, but i need the result to be editable. Sorry saying it just now, I've edited my post
– NoobPro
2 days ago

add a comment |

up vote
0
down vote

Create a Pivot Table from your data with the following configuration:

Pivot

You'll probably also need to format the value as a Time:

format

And you may also want to display the Pivot Table in tabular format to yield the desired result:

tabular

Also turning off the display of Subtotals & Grand Totals:

subtotals

grandtotals

Note that if there are multiple In/Out records per day per employee, this will only show the latest In or Out record (by virtue of aggregating with Max).

answered 2 days ago

Lee Mac

426110

Create a Pivot Table from your data with the following configuration:

Pivot

You'll probably also need to format the value as a Time:

format

And you may also want to display the Pivot Table in tabular format to yield the desired result:

tabular

Also turning off the display of Subtotals & Grand Totals:

subtotals

grandtotals

Note that if there are multiple In/Out records per day per employee, this will only show the latest In or Out record (by virtue of aggregating with Max).

answered 2 days ago

Lee Mac

426110

answered 2 days ago

Lee Mac

426110

answered 2 days ago

Lee Mac

426110

answered 2 days ago

Lee Mac

426110

Hey it works, but i need the result to be editable. Sorry saying it just now, I've edited my post
– NoobPro
2 days ago

add a comment |

Hey it works, but i need the result to be editable. Sorry saying it just now, I've edited my post
– NoobPro
2 days ago

Hey it works, but i need the result to be editable. Sorry saying it just now, I've edited my post
– NoobPro
2 days ago

add a comment |

NoobPro is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

NoobPro is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Bdtyktl