I'm trying to create a work schedule spreadsheet. I have two cells per work day, one with the start time, one with the end time. I have another cell that shows the hours worked. However, if a worker works over 5 hours, they have to take a half hour lunch. The scheduled hours show the additional half hour. Is it possible to subtract that half hour if the time scheduled is over 5 hours?
if you enter times as durations... like this:
"3h 25m"
then you can add and subtract
you can then do tests to see if you need to subtract a break:
C1=sum(A1:B1)
this is shorthand for... select cell C1 by single clicking the cell, then
type, or copy and paste from here, the formula:
=sum(A1:B1)
then type the return key
D1=C1−IF(C1>DURATION(weeks,days,5,minutes,seconds,milliseconds), "30m", "0m")
if you enter times as durations... like this:
"3h 25m"
then you can add and subtract
you can then do tests to see if you need to subtract a break:
C1=sum(A1:B1)
this is shorthand for... select cell C1 by single clicking the cell, then
type, or copy and paste from here, the formula:
=sum(A1:B1)
then type the return key
D1=C1−IF(C1>DURATION(weeks,days,5,minutes,seconds,milliseconds), "30m", "0m")
Thank you!
You are very welcome.
Schedule Creation in Numbers
