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

Time values that add up to greater than 24 hours

Hi Apple SUpport Community,


I am trying to get a cell in Numbers to return a TIME value when the sum of two time values are greater than 24.


For example, if a worker has a shift that starts at 8pm and works for 6 hours and 15 minutes, the shift ends at ((20:00+06:15=26:15)-24:00)=02:15am the next day.


If I formulate a cell in numbers to add to time values, it comes return the nasty red triangle saying "The Formula's arguments can only contain one date value"


Thanks in advance for any help!


ST

Posted on Aug 14, 2013 8:03 AM

Reply
5 replies

Aug 14, 2013 10:44 AM in response to SKYTORT

a duration can be obtained by subtracting two time values (which are technically the date and the time). durations are specified as:

User uploaded file

weeks, days, hours, minutes, seconds and milliseconds.




I do not know what you are trying to figure out...

- how long did the worker work? or

- when did the shift end?



can you state a question (without regard for the solution) so we can assist? Also a screen shot of what you enter and what you want as output would be helpful.


All that said you can subtract two times to get a duration. You can add durations to get another duration. You cannot add times. To specify that an entry is a time you enter in the format "0w 0d 30h 14m 22s 0ms" any of the entries with 0 may be omitted.


you can add durations in cells by using the "+" or "-" operator


as in A1-B1


where A1 contains "3h 45m" and B1 contains "1h 15m"


the result will be "2h 30m"

User uploaded file

Aug 14, 2013 11:31 AM in response to SKYTORT

Here's your example, revised to work correctly:


"20:00+06:15=26:15)-24:00)=02:15am the next day."


Aug 13, 2013 20:00 + 6h 15m = Aug 14, 2013 02:15


User uploaded file

Row 2 shows the full date and time entry in column A, the duration of the shift (as entered) in column B and the full result of the addition formula in column C.


Row 6 shows the same values and result.

Columns A and C have been formatted as Date and Time, Date: 'none', Time: 19:08

Column B has been formatted as Duration: 0:00


Regards,

Barry

Time values that add up to greater than 24 hours

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