I'm trying to round time up to the nearest 5 minutes (0.1 hours).
My tracking software gives me time as 4h 33m 21s.
Does anyone know a way to round that to 4.6? round, roundup don't seem to work.
Mac Studio (M5 Ultra, 2026)
There isn't a single step to this, but there are a couple of functions you can string together to get what you want.
First, as you've probably found, you can't ROUND() a date or duration - ROUND() only works on numeric values, so the first step is to convert the duration into a number of minutes. Fortunately there's the built-in function DUR2MINUTES() that will do this.
=DUR2MINUTES(C2) => 488
(based on 8h 7m 53s in C2, that equates to 488 minutes)
Once you have a number of minutes, you can round that to the nearest 5 minutes using MROUND():
=MROUND(DUR2MINUTES(C2),5) => 490
(MROUND takes a number and rounds it to the nearest multiple of the provided factor, in this case 5)
Finally, you can use the DURATION() function to convert the number of minutes into a new duration value.
All-in, it looks something like this (based on C2):
=DURATION(,,,MROUND(DUR2MINUTES(C2),5)) => 8h 10m
What you'll get initially is something like '490m', but you can use the standard cell formatting to set the duration format to show hours and minutes.
There isn't a single step to this, but there are a couple of functions you can string together to get what you want.
First, as you've probably found, you can't ROUND() a date or duration - ROUND() only works on numeric values, so the first step is to convert the duration into a number of minutes. Fortunately there's the built-in function DUR2MINUTES() that will do this.
=DUR2MINUTES(C2) => 488
(based on 8h 7m 53s in C2, that equates to 488 minutes)
Once you have a number of minutes, you can round that to the nearest 5 minutes using MROUND():
=MROUND(DUR2MINUTES(C2),5) => 490
(MROUND takes a number and rounds it to the nearest multiple of the provided factor, in this case 5)
Finally, you can use the DURATION() function to convert the number of minutes into a new duration value.
All-in, it looks something like this (based on C2):
=DURATION(,,,MROUND(DUR2MINUTES(C2),5)) => 8h 10m
What you'll get initially is something like '490m', but you can use the standard cell formatting to set the duration format to show hours and minutes.
Couple of edits based on my earlier post:
> I'm trying to round time up to the nearest 5 minutes (0.1 hours).
0.1 hours is 6 minutes, not 5.
5 minutes is 0.0833 hours
I was working on the 5 minute line, not the 0.1 hours, so please clarify which you actually want...
Second is that I used MROUND() to round to the nearest 5 minute interval, but then I noted you wanted to round UP. There's no option within MROUND() to do this - it always rounds to nearest.
So if you want to round UP, then you'll need to switch the MROUND() function to:
=ROUNDUP(DUR2MINUTES(C2)÷5,0)×5
This takes the DUR2MINUTES(C2) and divides it by 5 to find how many 5-minute windows there are (note: divide by 6 if you want 0.1 hours). This value is then rounded UP to the next whole number, then multiplied by 5 (or 6) to determine the number of minutes for the rounded-up value.
This leaves your final formula as:
=DURATION(,,,ROUNDUP(C2÷5,0)×5)
Got to a similar place with different units and functions. Thanks for taking the time to reply. I've never found much useful here but you've changed the narrative.
How to round time to the nearest 5 minutes?