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
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)
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
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.
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