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 |
