CODEWITHSUNDEEP
dateexcelspreadsheetvba
AbdullahR
AbdullahR

Reputation: 1041

Finding the duration of time spent at work excluding multi ins and outs

I have an excel sheet with thousands of records for employee ins and outs during work hours. I want to find the duration of time the employee spent at work. I need a way to calculate the difference between the first in and last out and ignoring records in between them. The sheet look something like this

Employee       In                 out
   A       1-Jan 6:30            1-Jan 9:30
   A       1-Jan 9:50            1-Jan 4:30
   A       1-Jan 4:40            1-Jan 5:30
   A       2-Jan 6:32            2-Jan 10:30
   A       2-Jan 11:00           2-Jan 4:25
   B           .                   .
   .
   .           .                   .

Any help would be appreciated

Upvotes: 0

Views: 206

Answers (3)

SeanC
SeanC

Reputation: 15923

There is a sneaky way by using VLOOKUP - The data will have to be sorted for this to work.

To find the start time, it's an easy =VLOOKUP("A",A2:C999,2,FALSE)
To find the end time, we have to find the last "A" record, and we do this by searching for a tiny bit more than "A": =VLOOKUP("A!",A2:C999,3,TRUE)
Note the TRUE in the VLOOKUP so it will return the next largest value that is less than what we search for, so it will return the last "A"

subtract one from the other, and we have the equation: 

=VLOOKUP("A!",A2:C999,3,TRUE)-VLOOKUP("A",A2:C999,2,FALSE)

Upvotes: 1

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

For employ 'A', assuming your columns are A:A to C:C (and dates are recognized as such, not text):

= SUMIF(A:A; "A"; C:C) - SUMIF(A:A; "A"; B:B)

Upvotes: 0

Ioannis
Ioannis

Reputation: 5388

Assuming the data is in columns A, B and C, and you want the duration for employee A in column D1, this formula will do it:

=MAX(IF(A1:A5="A",C1:C5))-MIN(IF(A1:A5="A",B1:B5))

After you enter the formula into the cell, instead of pressing Enter to escape the cursor, you need to press Ctrl+Shift+Enter to make it work.

I hope this helps?

Edit: I did not assume any headers, so data starts at row 1.

Upvotes: 1

Related Questions