Sum time value within a certain time range

**FDibbins** · 02-16-2014, 04:49 PM

Hi and welcome to the forum

for "normal" time, try this...
=IF(AND(OR(B3<7/24,B3>19/24),OR(B3>19/24,C3>19/24)),0,IF(B3<=7/24,IF(C3>19/24,19/24,C3)-7/24,IF(C3>19/24,19/24,C3)-B3))
For OT, try this...
=IF(B3<7/24,B3,0)+IF(C3>19/24,C3-19/24,0)

Test with a range of times and let me know how you make out?

**Marnixsjoerd** · 02-16-2014, 05:35 PM

Hi FDibbins! thank you for your quick response!

I entered the formulas into my spreadsheet, it works, but, once the times 06:00 to 12:00 are added in, then the normal time shows 5 and the OT shows 6. However, the OT should only show 1 and the rest should be under the normal time..

Same goes for 06:00 to 20:00, the "OT" time should only be 2, yet it shows 7, and the normal time is reduced to zero..

hope you can fix it! :D