How to Round Time in Excel

This Excel Tutorial is about rounding Time Up, Down and to Nearest 15, 30 or 60 minutes.

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")

Date and Time in Excel