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.

  • You do realise at no point have you specified the software you're using, nor the version of that software? The community cannot just assume you're using something like Numbers, Excel, OpenOffice Calc, etc and different versions offer different functionality, including new functions/formulas. – Monomeeth Dec 8 '16 at 22:11
  • 1
    The screenshot clearly shows he's using Numbers. – jksoegaard Dec 8 '16 at 22:12
  • @jksoegaard Yes, but it doesn't specify version. It's best practice to provide details because not everyone will be able to discern from a screenshot what the OP is using - it just so happens that Numbers has a somewhat unique interface so anyone familiar with it will know. For me personally though, the main issue is that the supported formulas/functions can differ depending on version and the community shouldn't have to try to piece together: Oh, the screenshot is from Numbers, and he's used the Sierra tag, and because Sierra is only new he's most likely using version 4.0.5 of Numbers. – Monomeeth Dec 8 '16 at 22:24
  • You're right - it is good practice to include the information. However the question as it stands is not really unsolvable without that specific information. I'm just pointing out to everyone that he's using Numbers. – jksoegaard Dec 8 '16 at 23:19

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.


enter image description here

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

| improve this answer | |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .