+ Reply to Thread
Results 1 to 5 of 5

Timesheet : Calculate time worked with multiple Ins and Out

  1. #1
    Registered User
    Join Date
    03-02-2021
    Location
    Abu Dhabi
    MS-Off Ver
    Office 365
    Posts
    3

    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
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    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.
    Quang PT

  3. #3
    Registered User
    Join Date
    03-02-2021
    Location
    Abu Dhabi
    MS-Off Ver
    Office 365
    Posts
    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. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    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. #5
    Registered User
    Join Date
    03-02-2021
    Location
    Abu Dhabi
    MS-Off Ver
    Office 365
    Posts
    3

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to calculate time worked: Start time, end time, lunch break
    By khalinguyen191999 in forum Excel General
    Replies: 1
    Last Post: 09-14-2020, 11:37 PM
  2. Replies: 7
    Last Post: 04-25-2020, 03:23 AM
  3. [SOLVED] Calculating multiple pay rates based on time of day worked, not number of hours worked
    By vdbonce in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-04-2020, 02:30 AM
  4. Timesheet to calculate TIL hrs based on Total Hours worked
    By fraser.v in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-16-2020, 08:45 AM
  5. Replies: 7
    Last Post: 06-27-2016, 01:11 AM
  6. [SOLVED] [SOLVED] Calculate time worked inside Regular Hours and Over Time
    By myxamhatosis in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-07-2015, 09:16 PM
  7. Timesheet, hours worked with multiple varriables...
    By biozombie in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-22-2015, 09:00 PM

Tags for this Thread

Bookmarks

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