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

1. ## 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.
Rick in California

2. ## 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. ## Re: Calculate hours worked using start and stop times

Thank you very much! That did the trick

4. ## Re: Calculate hours worked using start and stop times

You are welcome.