CODEWITHSUNDEEP
exceltime
alwbtc
alwbtc

Reputation: 29445

How to round time to nearest hour in Excel?

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

Upvotes: 12

Views: 60727

Answers (4)

Maxxarn
Maxxarn

Reputation: 111

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.

Upvotes: 11

Ken D.
Ken D.

Reputation: 1

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

Upvotes: 0

Stefan
Stefan

Reputation: 528

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

Upvotes: 1

barry houdini
barry houdini

Reputation: 46341

You can use MROUND function like this

=MROUND(A1,"1:00")

Upvotes: 21

Related Questions