Formula required to add time but must meet requirements

Question

Level 1

16 points

Formula required to add time but must meet requirements

Please could you help me to create a formula that will sum up different times based on certain criterion.

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

Let me explain.

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.

Any help would be much appreciated.

Regards

David

iMac, macOS Sierra (10.12.1), null

Posted on Dec 8, 2016 4:46 AM

Reply

Answer 1

Dec 8, 2016 5:58 PM in response to DaCord

Ive been working on it but can't completely give a short formula to do it. I'm thinking a if formula. Like if the row below is 24 add x,x,x. If not add x,x.

Just giving you a direction to look in. Hope it helps.

Reply

Answer 2

Dec 9, 2016 4:24 AM in response to DaCord

Here's another suggestion. Add a hidden colum that adds up F if the row below is > or = 24.

Here is a quick example.

then sum up the hidden rows cell with the cell in B.

Reply

Answer 3

DaCord Author

Level 1

16 points

Dec 9, 2016 11:43 AM in response to chchchScott

Hi Scott

I like your idea but second formula does not work. The total rest should be 82 hours. D10 must be added to F6.

Sorry any other way we could tweak this to add the extra 5hrs in.

Reply

Answer 4

Dec 9, 2016 4:17 PM in response to DaCord

Not that I can see. Since the days off appear to be random, all I can think of is to use the formula >= 24. Then add in the rest hours from the last day manually every time. If I think of something else I'll let you know.

Reply

Answer 5

Dec 9, 2016 4:37 PM in response to DaCord

I Think I figured it out.

If you add an IF Statement saying add cell if it's < 24 it'll add that 5 to the next day then carry it up.

Reply

Answer 6

DaCord Author

Level 1

16 points

Dec 10, 2016 5:33 AM in response to chchchScott

That works but now if you add the minutes in at Start and Finish Time the end result is a number which I cannot have. It must be duration. I've tried changed the ranges all to be duration but still comes out as number. Most frustrating when your idea is so good.

Any other ways around this please.

Cheers

Reply

Answer 7

Dec 10, 2016 5:53 AM in response to DaCord

I don't know anything about using time functions. Maybe someone else can help with that. I'm glad my ideals got you close. Good luck.

Reply

Answer 8

Yellowbox

Level 7

21,179 points

Dec 10, 2016 6:48 AM in response to chchchScott

Hi chchchScott,

I don't know anything about using time functions. Maybe someone else can help with that

You are going well. Perhaps DaCord is facing the "crossing midnight" problem.

Numbers has a Date & Time format. When a time is entered in a cell, it contains the date when it was entered (even if the cell's Data Format hides the date, the date is still stored by Numbers). Adding "1d" (one day) may help to "cross midnight".

It is late here. I hope to join in tomorrow, but I won't try to take over from your excellent replies.

Keep up the good work 🙂.

Regards,

Ian.

Reply

Answer 9

DaCord Author

Level 1

16 points

Dec 10, 2016 11:46 AM in response to chchchScott

Solved it but Scott you must take the credit. Here's how I've solved the number format. You have to change the false to DURATION after the comma. By leaving it as false it will return a number.