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: 4 Responses to “How Can I Create a Time Duration Formula and Also Show a Rounding-up Result?”

    9 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
    8 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

    scott levin
    2 months ago

    I would like to create a time in column and a time out column (using 24hr time) and then have a third column that calculates the hours and minutes that I have worked. Keep in mind that sometimes I work over night and the inputs would be something like 2000 and 0720. How do I calculate the time worked in hours and minutes.

    2 months ago

    scott: You subtract. Subtract the time in from the time out. The result would be a duration. Then format the cell with the duration to show only hours and minutes. See https://macmost.com/calculations-with-time-and-duration-values-in-numbers.html

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)