Forum Discussion
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...
Thanks Hans, this has worked perfectly and I will now amend the spreadsheet to reflect the new formula, Many thanks again
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
(Night shifts typically atart at 19:45 until 07: 45 or 20:00 to 08:00)
Joe
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))
Hello Hans,
Many thanks for the formula, there are people working from 8 to 12 hr shift get 1 Hour break.
How do incorporate that into this formula?
Day -0 to 7Hrs = No Break
Day -7 - 8 hrs = 30mnts
Day 8 - 12 Hrs = 1hr Break
Night Shift = No Break
Using the same sheet as reference
Regards
KuriWhat defines a night shift?
- Hello Hans,
Yes, I have tested with various times and it works perfectly, thank you very much for your halp again, Joe