Forum Discussion

joem1964

Copper Contributor

Apr 14, 2021

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, b...

ROSTER FORMULAE.xlsx42 KB

excel

HansVogelaar
Apr 14, 2021
joem1964

In H7:

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

joem1964

Copper Contributor

Apr 14, 2021

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

joem1964

Copper Contributor

Apr 22, 2021

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