This Excel Tutorial is about rounding Time Up, Down and to Nearest 15, 30 or 60 minutes.
Table of Contents
Round Time Up
The CEILING function in Excel will round a number up to the nearest multiple of a given number. This logic can be used to round time ‘up’ to the nearest multiple of given Minutes or Hours.
To round 6:13 AM up to multiple of 15 minutes, use the following formula.
=CEILING("6:13 AM","0:15")
6:13 AM will be rounded to 6:15 AM.
To round 6:13 AM up to 6:15 AM,
=CEILING("6:13 AM","0:30")
To round 6:13 A.M up to multiple of 60 minutes (1 hour),
=CEILING("6:13 AM","1:00")
6:13 AM will be rounded to 7:00 AM.
Round Time Down
The FLOOR function in Excel will round a number down to the nearest multiple of a given number. The same logic can be used to round time ‘down’ to the nearest multiple of given Minutes or Hours.
To round 6:13 AM down to multiple of 15 minutes, use the following formula.
=FLOOR("6:13 AM","0:15")
6:13 AM will be rounded to 6:00 AM.
To round 6:13 AM down to 6:00 AM,
=FLOOR("6:13 AM","0:30")
To round 6:13 AM down to multiple of 60 minutes (1 hour),
=FLOOR("6:13 AM","1:00")
Round Time to nearest 15 minutes
The MROUND function in Excel will round a number to the nearest multiple of a given number. MROUND function will round the number Up or Down according to the nearest multiple.
To round 6:13 AM to the nearest multiple of 15 minutes,
=MROUND("6:13 AM","0:15")
6:13 AM will be rounded to 6:15 AM.
Note that =MROUND(“6:07 AM”,”0:15″) will return ‘6:00 AM’ and =MROUND(“6:08 AM”,”0:15″) will return ‘6:15 AM’.
To round 6:13 A.M to the nearest multiple of 60 minutes/1 hour,
=MROUND("6:13 AM","1:00")
