Formula required to add time at rest

numbers

How can I create a formula that will sum up different times based on certain criteria below?

The below table is an example of what my current formula looks like, which is very basic and is changed on a daily basis.

B12 = 8:00 Start of shift, C12 = 19:10 End of Shift
From End of shift to midnight F12 = 4:50
I never work on the 17/11/16 and the 18/11/16 which totals 48 hours. The clock restarts at midnight 0:00 hrs
I returned back to work on the 19/11/16 at 9:25
So the total rest from work would be 62:15 hrs G9 = SUM(F10:F12)+B9

In the G column I have to change the formula to suit each day to get the total rest from finish to start of next shift.

If we look at the next Rest time G8 this is SUM(F9)+B8 = 13:30 and so on.

The formula is required in the G column so I can do away with the manual entry each day. I want the formula to sum these ranges by some form of indexing criteria.

Best Answer

I have solve the above formula requirement. I posted this on discussions.apple.com. Link to discussion is https://discussions.apple.com/thread/7778822

I had to add a hidden column H:H, then the formula

=IF(F3=DURATION(,,24,0,,),F3+H3,DURATION(,,0,0,,)) +IF(F3< DURATION(,,24,0,,),F3,DURATION(,,0,0,,))

I then copied formula all the way down column H

enter image description here

The second formula in I:I column is written so that it only returns a time if there is a time in the B:B range.

=IF(B2="","",B2+H2)

enter image description here

I hope this helps someone. It has really made my life easier.

Related Question