How to round time to nearest hour in Excel?

Question

How to round time to nearest hour in Excel, for example:

67:45:00 will be 68:00:00
and
53:14:00 will be 53:00:00

regards

Answer 1

You can use MROUND function like this

=MROUND(A1,"1:00")

Answer 2

Assuming the time is in A1 this will round to closest hour.

=ROUND(A1*24,0)/24

---

• Using ROUNDDOWN will force 67:45:00 to 67:00:00
• Using ROUNDUP Will force 67:45:00 to 68:00:00

Same formula, change:

• 24 to 48 for half hours.
• 24 to 96 for quarters

  ---

If you are grouping hourly in a 24 hour span but the date is included in the time stamp, use the same formula but subtract the datevalue after:

=ROUNDDOWN(A1*24;0)/24-INT(A1)

This is useful if you want to see at what time of day something peaks over a period of time.

Answer 3

Transform it to hours (5h 15m = 5.25h) then round it

if you only have it as a string use

=if(round(mid(A1;4;2);0)>29;mid(A1;1;2)+1&":00:00";mid(A1;1;2)&":00:00")

i use round to convert the minutes into a number