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

http://stackoverflow.com/questions/1372924/round-time-to-nearest-15min-interval-in-excel — Jonathon Reinhart, Nov 19 '13 at 08:03
possible duplicate of [Round minute down to nearest quarter hour](http://stackoverflow.com/questions/2480637/round-minute-down-to-nearest-quarter-hour) — , Nov 19 '13 at 11:28

barry houdini · Accepted Answer · 2013-11-19T11:25:14.567

20

You can use MROUND function like this

=MROUND(A1,"1:00")

edited Nov 19 '13 at 11:25

answered Nov 19 '13 at 08:30

barry houdini

Hey.. is this related to any other config in the spreadsheet? It rounds too high in my setting. – LuizAngioletti Jun 06 '17 at 20:30
Same as `=MROUND(A1,1/24)` – OGCJN Sep 21 '19 at 18:22

score 10 · Answer 2 · answered Apr 24 '15 at 13:41

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

=ROUND(A1*24,0)/24

Same formula, change:

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.

Stefan · Answer 3 · 2013-11-19T12:51:12.883

1

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

edited Nov 19 '13 at 12:51

answered Nov 19 '13 at 08:18

Stefan

score 0 · Answer 4 · answered Mar 04 '15 at 23:00

I recently had to convert times to the nearest quarter hour. I used the following sequence of formulas, which seemed to work:

=SUM(A1*24*60) - this converts the time to minutes

=MOD(B1,15) - this finds the minutes since the last quarter hour

=IF(C1>7,15-C1,-C1) - minutes needed to round up or down to nearest quarter hour

=SUM(D1/(24*60)) - converts the adjustment needed from minutes back to a days

=SUM(A1+E1) - this is the original time adjusted up or down to the nearest quarter hour

4 Answers4