Calculate hours worked using start and stop times
Results 1 to 4 of 4

Thread: Calculate hours worked using start and stop times
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2014
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculate hours worked using start and stop times

    The goal is to calculate the number of hours worked based on entries for start time and stop time.

    The problem is that it automatically deducts 0.5 hours, unless, the start and stop times cross over 2400.

    For example: Start Time 0600 with Stop Time 1800 will show 11.5 Hours Worked. However, Start Time 1800 with Stop Time 0600 will show 12 Hours Worked.

    I never want 0.5 hours deducted from the total.

    B13 is number of hours worked.
    C13 is start time entered as text using 24-hour time (e.g., 0600).
    D13 is end time entered as test using 24-hour time (e.g., 1800).
    G13 and H13 are in hidden columns.

    The formula in B13:
    =IF(C13="","",IF(D13="","",(((LEFT(D13,2)&":"&RIGHT(D13,2))+0+H13)-((LEFT(C13,2)&":"&RIGHT(C13,2))+G13))*24))

    The formula in G13:
    =30/1440*AND(((LEFT(C13,2)&":"&RIGHT(C13,2))+0)<=0.5,((LEFT(D13,2)&":"&RIGHT(D13,2))+0)>(0.5+30/1440))

    The formula in H13:
    =--AND(((LEFT(D13,2)&":"&RIGHT(D13,2))+0)<0.5,((LEFT(C13,2)&":"&RIGHT(C13,2))+0)>0.5)

    For 0600 to 1800 the value in G13 is 0.02 and in H13 is 0.00
    For 1800 to 0600 the value in G13 is 0.00 and in H13 is 1.00

    I don’t understand these formulas enough to edit them.
    Thanks in advance,
    Rick in California

  2. #2
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,356
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Calculate hours worked using start and stop times

    See if the following formula for cell B13 works for you. It does not need any helper columns:

    =(TEXT(--D13,"00\:00")-TEXT(--C13,"00\:00")+(C13>D13))*24

  3. #3
    New Member
    Join Date
    Jan 2014
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate hours worked using start and stop times

    Thank you very much! That did the trick

  4. #4
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,356
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Calculate hours worked using start and stop times

    You are welcome.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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