Calculate hours worked using start and stop times

Rick93636

New Member
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

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

Tetra201

MrExcel MVP
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

Rick93636

New Member
Thank you very much! That did the trick

You are welcome.

Replies
0
Views
155
Replies
1
Views
136
Replies
3
Views
65
Replies
2
Views
414
Replies
2
Views
166

1,191,313
Messages
5,985,932
Members
439,988
Latest member
JDayco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back