Excel has numerous rounding functions as expected from a spreadsheet software. However, rounding time values may not be as straightforward as rounding numbers. In this guide, we’re going to show you how to round time values in Excel.

## Date and time concept in Excel

Let's say you want to round a time value to the nearest hour. You cannot use the regular rounding functions like **ROUND, ROUNDUP **or **ROUNDDOWN** because of their decimal based limitation. You may think of using a 60-base numbers or multipliers with functions like **MROUND, CEILING.MATH** or **FLOOR.MATH**. Although the approach sounds rational, the results will be wrong thanks to the Excel's date/time value evaluation.

Excel keeps date and time values as *numbers*. According to Excel, the history starts from Jan 1st, 1900 and accepts this date as 1. While whole numbers represent days, the decimal represents time. For example, while 1/1/2018 is equal to 43101, 12:00 is equal to 0.5. The date or time value you see in a worksheet is no more than a *number formatting*.

You can easily test this. Enter a time to cell. You will see that the cell's number formatting is set to *Time* automatically. Converting the number format to *General* will show you the exact number.

So, you need to round time values by numbers that Excel accepts. Thankfully, you do not need to calculate these numbers by yourself. Excel's **TIME** formula can return *time number* by given *hour, minute *and *second* values.

## How to round time values

Since we have reviewed the basic concept of representing time values in Excel, let's see how you can round them. The **MROUND, CEILING.MATH** or **FLOOR.MATH** formulas will be wise choices thanks to their ability to calculate by multiplier/significance.

For general rounding through both sides, choose the **MROUND**. Otherwise choose between **CEILING.MATH** or **FLOOR.MATH** to round up or down respectively. The trick is to use the **TIME **function to return a valid time value as a multiplier/significance. Thus, the syntax will be the following:

For example, to round the nearest 15 seconds, the rounding function should be the **MROUND** and the *15 seconds* should be defined as *TIME(0,0,15)*.

Based on this logic here are some examples:

Nearest minute |
=MROUND(<time>,TIME(0,1,0)) |
10:29:08 PM | 10:29:00 PM |

Next 15 minutes |
=CEILING.MATH(<time>,TIME(0,15,0)) |
11:29:08 PM | 11:30:00 PM |

Previous hour |
=FLOOR.MATH(<time>,TIME(1,0,0)) |
1:29:08 AM | 1:00:00 AM |