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

How Can I Create a Time Duration Formula and Also Show a Rounding-up Result?

Clarity to my initial question: How can I have a “Time In” in one column and a “Time Out” in the next column while having the “Duration” formula in the next column, then have a final column where it rounds the Duration up to the nearest quarter-hour? I enter a formula for the rounding-up, however, Numbers states “Argument 1 of CEILING can’t be a duration” (CEILING being the choice of formatting I was told to use). See link here (https://imgur.com/a/oqffy8U)

Looking to find the duration between a start and a stop time while having another result showing a rounded-up time to the nearest quarter hour.
—–
Eric

Comments: 2 Responses to “How Can I Create a Time Duration Formula and Also Show a Rounding-up Result?”

    5 months ago

    CEILING can't use a duration because it doesn't know what to round up to (minutes? hours?). So convert the duration to a number using one of the many function like DUR2MINUTES to get a hard number. So 7 instead of 7m, or 127 instead of 2h 7m. Then use the CEILING function on that. I don't know what you want to round up to, but I'll assume 10 minute intervals. So:

    =CEILING(DUR2MINUTES(D2),10)

    If you want that as a duration, then multiply by a single unit for that duration, in this case minutes. You can get 1m using the DURATION function. So like this:

    =CEILING(DUR2MINUTES(D2),10)*DURATION(0,0,0,1,0,0)

    Eric
    4 months ago

    Hello Gary,

    I meant to get back to you earlier on this, my apologies. Thank you so much. The initial formula worked perfectly for my needs. I exchanged the '10' for '15' to accomplish the next quarter hour increment for billing. Do you want me to share a sample of the spreadsheet in any way to help anyone else? This seems to be a common question out there as other service-related companies like ours does similar billing. Happy to pass this along.

    My thanks again,
    Eric

Leave a New Comment Related to "How Can I Create a Time Duration Formula and Also Show a Rounding-up Result?"

:
:
:
0/500 (500 character limit -- please state your comment succinctly and do not try to get around this limit by posting two comments)