5 Replies Latest reply: Aug 15, 2013 7:51 PM by SKYTORT
SKYTORT Level 1 Level 1 (0 points)

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

  • 1. Re: Time values that add up to greater than 24 hours
    Jeffrey Jones2 Level 6 Level 6 (8,510 points)

    6:15 isn't — or rather, it shouldn't be — a time value. It is a duration. What you are saying is "Add 8 pm plus 6:15 am", which, of course, doesn't make sense. Format 20:00 as time and 6h 15m as duration.

  • 2. Re: Time values that add up to greater than 24 hours
    Wayne Contello Level 6 Level 6 (13,620 points)

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

    Screen Shot 2013-08-14 at 12.36.51 PM.png

    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"

    Screen Shot 2013-08-14 at 12.44.02 PM.png

  • 3. Re: Time values that add up to greater than 24 hours
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi SKYTORT,

     

    In Numbers, a Date-And-Time format is exactly that. A Date has a Time attached, and a Time has a Date attached. The cell format can display either the Date or the Time or both. A Duration is a difference between two events and the cell can display any period of difference, from weeks to milliseconds.

     

    Regards,

    Ian.

  • 4. Re: Time values that add up to greater than 24 hours
    Barry Level 7 Level 7 (29,180 points)

    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

     

    Screen Shot 2013-08-14 at 11.28.29 AM.png

    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

  • 5. Re: Time values that add up to greater than 24 hours
    SKYTORT Level 1 Level 1 (0 points)

    Hi All,

     

    Thanks a lot to all of you guys for taking the time and effort to clarify things for me.

     

    I made the stupid mistake of formatting all cells as time, whereas the "time worked" is obviously a duration...

     

    All works fine now!!

     

    Regards

    ST