MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How Do I Subtract Time Durations In Numbers?

Hello Macmost.

Sorry for my English I am from Danmark.
I’m trying to make a schedule that includes lunch breaks so I need to calculate three different times.
(=sum 20:15 – 1:15 – 7:00 equals error). Why?

Please help
Erling Therkelsen

Comments: One Response to “How Do I Subtract Time Durations In Numbers?”

    3 years ago

    Your formula has a couple of errors. Using “sum” doesn’t make any sense here. But I can see how you would think that 20:15-1:15-7:00 should work. But you get the error. You can click on the error mark (triangle with !) to get an explanation. It states: “The operator – cannot subtract a date from a number.”
    This give you a clue as to what is happening. 20:15-1:15 gives you an answer as a duration. You are subtracting one time from another. Then you try to subtract a duration from a time and it fails.
    The problem is that you are giving three TIMES to a formula. You want to give 3 DURATIONS to a formula. Right? A time is a specific place marker, actually called a “date” in Numbers. But a duration is a length of time.
    So suppose I have 20:15 in B2, 1:15 in C2 and 7:00 in D2. Then in E2 I put the formula =B2-C2-D2. I get the error.
    The problem is that the text 20:15 is interpreted as the TIME 20:15. In order to correctly enter a duration, you need to use the format “20h 15m.” So the proper values are:
    B2 is 20h 15m
    C2 is 1h 15m
    D2 is 7h 0m
    Then in E2 the formula =C2-D2-E2 gives you 12h 0m.

Comments Closed.