# Timesheet : Calculate time worked with multiple Ins and Out

1. ## Timesheet : Calculate time worked with multiple Ins and Out

Hey excel Gurus,
Long time lurker who's learnt alot from you in the past - now creating an account to post one that stumped me !

So i have a database of time Ins and Outs, per day. calculating the time between First in and Last out is easy, Time(a1-b1, "hh") format.
Where it gets complicated, is multiple Time Ins and Outs, within the same day

Below is an example :

Date Time Location Remarks
1-Sep-20 9:40 Main Staff Entry Car Barrier (IN) First in, time starts from now "This is easy, time spent for the day is 7:05 = 16:45 (-) 9:40"
1-Sep-20 9:50 Entry Barrier L1 (IN) Ignore this, since it's 2nd In
1-Sep-20 9:51 Staff Entry Barrier L3 (IN) Ignore this, since it's 3rd In
1-Sep-20 16:45 Staff Entry Barrier L1 (OUT) First Out, time captured
1-Sep-20 16:45 Staff Entry Barrier L3 (OUT) Ignore since 2nd Out.

New day

2-Sep-20 12:48 Main Staff Entry Car Barrier (IN) First in, time starts from now This is tricky, time is total 5:36, calculated as : ((3:02 = 15:50-12:48)+ (2:34 = 18:28 - 15:50))
2-Sep-20 12:55 Staff Entry Barrier L3 (IN) Ignore this, since it's 2nd In
2-Sep-20 15:50 Staff Entry Barrier L1 (OUT) Capture this, since it's 1st out
2-Sep-20 15:54 Staff Entry Barrier L3 (IN) Capture this since its 1st In Again
2-Sep-20 18:28 Staff Entry Barrier L1 (OUT) Capture this since its 2nd Out again

New day

3-Sep-20 8:25 Main Staff Entry Car Barrier (IN) First in, time starts from now Another Trickey one. Total time is 7:05 = ((2:01=10:26-8:25)+(5:04=15:32-10:28))
3-Sep-20 8:32 Staff Entry Barrier L3 (IN) Ignore this, since it's 2nd In
3-Sep-20 10:26 Staff Entry Barrier L1 (OUT) Again, Capture this since it's 1st Out
3-Sep-20 10:27 Main Staff Exit _ Executive Gate (OUT) Ignore since 2nd Out.
3-Sep-20 10:28 Main Staff Entry _ Executive Gate (IN) Capture again, 2nd In for the day
3-Sep-20 10:29 Staff Entry Barrier L3 (IN) Ignore, 2nd In at new gate
3-Sep-20 15:32 Staff Entry Barrier L1 (OUT) Capture final out

Any leads or suggestions >? I'm sure there's a way with Sumproduct (a formula i detest) or a nested if. Please do let me know
I've attached the file and a snapshot

Thanks
Localmode

2. ## Re: Timesheet : Calculate time worked with multiple Ins and Out

Not sure If I can catch you, but try in L4 then drag down:

``Please Login or Register  to view this content.``

3. ## Re: Timesheet : Calculate time worked with multiple Ins and Out

Thanks so much ! seems to work except for 23rd December. Can you please explain the formula - it's so very elegant

4. ## Re: Timesheet : Calculate time worked with multiple Ins and Out

Cell L20
Firstly, see condition statement:
=IF(OR(COUNTIF(E20,"*(OUT)*")=0,COUNTIF(E19,"*(OUT)*")),""
mean: L20 is blank if there is not "OUT" exist in E20, or if there is "OUT" in upper cell
else: D20-Time In
Time In must be the 1st IN after the last OUT.
Last OUT:ISNUMBER(SEARCH("OUT",\$E\$3:E19)) return 1/0
Return next IN after last OUT: LOOKUP(2,1/ISNUMBER(SEARCH("OUT",\$E\$3:E19)),\$D\$4:D20)
Then, If there is not last OUT (1st occurence of OUT) then return 1st IN
IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH("OUT",\$E\$3:E19)),\$D\$4:D20),\$D\$4)

The final formula in L20:
=IF(OR(COUNTIF(E20,"*(OUT)*")=0,COUNTIF(E19,"*(OUT)*")),"",D20-IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH("OUT",\$E\$3:E19)),\$D\$4:D20),\$D\$4))

P/S: There is no "23rd Dec data" in the sample file so I could not check if it work. Could you attach a sample file with new data for 23rd Dec?

5. ## Re: Timesheet : Calculate time worked with multiple Ins and Out

My apologies i had only uploaded a portion of the data.
Thankyou so much Bebo, this was a real eye opener for me. I particularly like the Condition statement.
Just to be sure, i split up the same formula into multiple columns and tried the same

Thanks again and have a great week ahead

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1