 SOLVED

# Help with calculating empty Time data in cells

I am attempting to create a Roster Spreadsheet to calculate hours workedin a week (the hours worked are calculated in a hidden field), The tempolate must cater for days off , i.e. no times entered, but the formulae needs to be able to cater for this or else #NUM appears
18  19  20  21
Sun  Mon  Tues  Wed
Hours Worked Start End Start End Start End Start End
#NUM!   20:00 8:00 8:00 20:00
#NUM!  11:30  11:30
The daily hours worked are totaled in the hours worked column.

A second issue I have is when the hours worked is < 7 hours , then the lunch break is not deducted.
5 Replies
best response confirmed by joem1964 (New Contributor)
Solution

# Re: Help with calculating empty Time data in cells

In H7:

=IF(OR(H6="",I6=""),0,MROUND(MOD(I6-H6,1)-(MOD(I6-H6,1)>=7/24)*\$F6/1440,1/1440))

# Re: Help with calculating empty Time data in cells

Thanks Hans, this has worked perfectly and I will now amend the spreadsheet to reflect the new formula, Many thanks again

# Re: Help with calculating empty Time data in cells

Hello Hans, Many thanks for your help and this resolved the issue for me, however, I now need to also cater for night shifts, apparently they work 12 hour shifts without the deduction of Breaks, can that be incorporated in the same formula?
(Night shifts typically atart at 19:45 until 07: 45 or 20:00 to 08:00)

Joe

# Re: Help with calculating empty Time data in cells

Does this do what you want?

=IF(OR(H6="",I6=""),0,MROUND(MOD(I6-H6,1)-(MOD(I6-H6,1)>=7/24)*(I6>H6)*\$F6/1440,1/1440))

# Re: Help with calculating empty Time data in cells

Hello Hans,

Yes, I have tested with various times and it works perfectly, thank you very much for your halp again, Joe