Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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.

User uploaded file

Regards


David

iMac, macOS Sierra (10.12.1), null

Posted on Dec 8, 2016 4:46 AM

Reply
11 replies

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.

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.


User uploaded file


Then to hide the I column I did the following.

User uploaded file


So between the 2 of us we seemed to have solved this.


Many thanks for your help Scott.

Formula required to add time but must meet requirements

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.