Microsoft corporation Excel 2013 32/64-b...

Hi Guys, I have a query here while calculating out the minimum value from a table of entries.

Below is the format in which I have my data.

Column A : Employee ID(Unique)

Column B: Employee Name

Column C: Time Stamp

Column D : In/Out

There are 17940 entries in this and this data is for the entire week. That means multiple entries in a single day per employee and these type of entries for 5 days of the week in a single excel sheet.Now I want to see that off all the time an employee went In and Out of the office premises, what was his/her First In and Last Out each day in fresh columns.

✔ Best Answer

Taking a shot here... You've got data like this. I'm only using 1 date, but I know you have many more. I'll give you the concept, you work out the rest.

A B C D 1 ID Name Date In/Out 2 135 Tom 1/1/2016 8:00 3 246 Sue 1/1/2016 9:00 4 579 Mary 1/1/2016 10:00 5 135 Tom 1/1/2016 17:00 6 246 Sue 1/1/2016 18:00 7 579 Mary 1/1/2016 19:00 8 135 Tom 1/1/2016 7:30 9 246 Sue 1/1/2016 10:30 10 579 Mary 1/1/2016 11:30 11 135 Tom 1/1/2016 18:30 12 246 Sue 1/1/2016 19:30 13 579 Mary 1/1/2016 20:30

Create a table that contains all names for each date. Again, I'm only using 1 date, you'll have a Name and Date for every date.J K L M N 1 ID Name Date Min Max 2 135 Tom 1/1/2016 3 246 Sue 1/1/2016 4 579 Mary 1/1/2016In M2, enter this array formula using Ctrl-Shift-Enter and then drag it down:

=MIN(IF($B$2:$B$13=K2,IF($C$2:$C$13=L2,$D$2:$D$13)))

Ctrl-Shift-Enter will add the {} around the formula.

What it should do is find the MIN value of D2:D13 for B2:B13=K2 (Tom) and C2:C13 = L2 (1/1/2016). That should be your earliest In time

Repeat in Column N for MAX.

Let us know how that works out for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Ya I have date and time stamp. Sorry I didnt mentioned it properly earlier.

Please post a small representative sample of your spread sheet, but first

read this How-To, which explains the use of the < PRE > tags, to align your data

and please use Column Letters and Row numbers.http://www.computing.net/howtos/sho...

MIKE

Taking a shot here... You've got data like this. I'm only using 1 date, but I know you have many more. I'll give you the concept, you work out the rest.

A B C D 1 ID Name Date In/Out 2 135 Tom 1/1/2016 8:00 3 246 Sue 1/1/2016 9:00 4 579 Mary 1/1/2016 10:00 5 135 Tom 1/1/2016 17:00 6 246 Sue 1/1/2016 18:00 7 579 Mary 1/1/2016 19:00 8 135 Tom 1/1/2016 7:30 9 246 Sue 1/1/2016 10:30 10 579 Mary 1/1/2016 11:30 11 135 Tom 1/1/2016 18:30 12 246 Sue 1/1/2016 19:30 13 579 Mary 1/1/2016 20:30

Create a table that contains all names for each date. Again, I'm only using 1 date, you'll have a Name and Date for every date.J K L M N 1 ID Name Date Min Max 2 135 Tom 1/1/2016 3 246 Sue 1/1/2016 4 579 Mary 1/1/2016In M2, enter this array formula using Ctrl-Shift-Enter and then drag it down:

=MIN(IF($B$2:$B$13=K2,IF($C$2:$C$13=L2,$D$2:$D$13)))

Ctrl-Shift-Enter will add the {} around the formula.

What it should do is find the MIN value of D2:D13 for B2:B13=K2 (Tom) and C2:C13 = L2 (1/1/2016). That should be your earliest In time

Repeat in Column N for MAX.

Let us know how that works out for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Hi Derby/Mike, The data in my spread sheet is a bit different from the way you have shown in last message. However, I align data in my spreadsheet in the same format as you have mentioned and tried using the exact formula you provided. This formula works good for the first row but when I am dragging this further, all it gives is 0.

Here is the sample of my spreadsheet.

A B C D 1 ID Name Stamp In/Out 2 CON-0001 Tom 2016-07-18 11:24:33 In 3 CON-0001 Tom 2016-07-18 11:41:16 Out 4 CON-0001 Tom 2016-07-18 11:52:47 In 5 CON-0001 Tom 2016-07-18 20:21:25 Out 6 CON-0001 Tom 2016-07-19 11:04:51 In 7 CON-0001 Tom 2016-07-19 12:59:54 Out 8 CON-0001 Tom 2016-07-19 18:57:04 In 9 CON-0001 Tom 2016-07-19 19:56:44 Out 10 CON-0002 Mary 2016-07-18 12:30:17 In 11 CON-0002 Mary 2016-07-18 14:21:47 Out 12 CON-0002 Mary 2016-07-18 15:51:32 In 13 CON-0002 Mary 2016-07-18 16:39:01 Out 14 CON-0002 Mary 2016-07-19 12:54:04 In 15 CON-0002 Mary 2016-07-19 16:04:49 In 16 CON-0002 Mary 2016-07-19 18:04:41 Out 17 CON-0002 Mary 2016-07-19 18:19:07 In 18 CON-0002 Mary 2016-07-19 19:38:24 Out 19 CON-0007 Niza 2016-07-18 11:28:10 In 20 CON-0007 Niza 2016-07-18 12:06:21 Out 21 CON-0007 Niza 2016-07-18 13:37:59 Out 22 CON-0007 Niza 2016-07-18 17:46:07 In 23 CON-0007 Niza 2016-07-18 18:23:08 Out 24 CON-0007 Niza 2016-07-19 11:10:02 In 25 CON-0007 Niza 2016-07-19 13:34:43 Out 26 CON-0007 Niza 2016-07-19 18:14:12 In 27 CON-0007 Niza 2016-07-19 18:23:26 Out

message edited by ArjunBadhan

Your "stamp" combines Date and Time in the same column. That will not work with my suggested formula. The Dates and Times must be in separate columns since the formula is comparing the Times in one column based on a specific Date in another. In other words, it is trying to find the MIN (or MAX) for a given Name

for each unique Date. Therefore, the array formula must see a range of Dates in a separate column.If you separate Column C into C and D, I believe that you should be OK. I tried that and ended up with this:

Tom 7/18/2016 11:24:33 Mary 7/18/2016 12:30:17 Niza 7/18/2016 11:28:10 Tom 7/19/2016 11:04:51 Mary 7/19/2016 12:54:04 Niza 7/19/2016 11:10:02

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Hi Derby,

This worked perfectly. I did it on the entire data and got the desired results.

Thanks man for your assistance.

Ask Your Question

Weekly Poll