Time values in Excel are stored as decimal numbers between 0 and 1, where 0 represents 12:00:00 AM and 1 represents 11:59:59 PM. For example, the time value 0.5 represents 12:00:00 PM or noon. This allows us to perform calculations with time values, such as adding, subtracting, multiplying, or dividing them.
However, sometimes we may need to round time values to a certain level of precision, such as the nearest hour, minute, or second. For example, we may want to round the time value 0.354166667, which represents 8:29:59 AM, to 0.333333333, which represents 8:00:00 AM. This can help us to simplify our data, make it more consistent, or match it with other sources.
There are several ways to round time values in Excel, depending on the desired level of precision and direction of rounding. In this article, we will explain the basic theory and procedures of rounding time values in Excel, and provide a comprehensive explanation with examples and tables. We will also explore some alternative approaches that may suit different scenarios.
The basic theory of rounding time values in Excel is to use a combination of mathematical functions and time formats. The mathematical functions can round the decimal numbers to the nearest multiple of a given number, while the time formats can display the rounded numbers as time values.
There are three main mathematical functions that we can use to round time values in Excel:
- MROUND: This function rounds a number to the nearest multiple of another number. For example, MROUND(0.354166667, 0.25) returns 0.25, which is the nearest multiple of 0.25 to 0.354166667. We can use this function to round time values to the nearest hour, minute, or second, by specifying the appropriate multiple as the second argument. For example, to round time values to the nearest hour, we can use MROUND(time, 1/24), where 1/24 represents one hour as a decimal number.
- CEILING: This function rounds a number up to the nearest multiple of another number. For example, CEILING(0.354166667, 0.25) returns 0.5, which is the next higher multiple of 0.25 from 0.354166667. We can use this function to round time values up to the nearest hour, minute, or second, by specifying the appropriate multiple as the second argument. For example, to round time values up to the nearest hour, we can use CEILING(time, 1/24), where 1/24 represents one hour as a decimal number.
- FLOOR: This function rounds a number down to the nearest multiple of another number. For example, FLOOR(0.354166667, 0.25) returns 0.25, which is the next lower multiple of 0.25 from 0.354166667. We can use this function to round time values down to the nearest hour, minute, or second, by specifying the appropriate multiple as the second argument. For example, to round time values down to the nearest hour, we can use FLOOR(time, 1/24), where 1/24 represents one hour as a decimal number.
The procedures of rounding time values in Excel using these functions are as follows:
- Enter the time value in a cell, or refer to a cell that contains the time value. For example, enter 8:29:59 AM in cell A1, or refer to cell A1 in the formula.
- Enter the desired mathematical function in another cell, and use the time value as the first argument. For example, enter =MROUND(A1, 1/24) in cell B1, or =CEILING(A1, 1/24) in cell C1, or =FLOOR(A1, 1/24) in cell D1.
- Enter the desired multiple as the second argument of the function, depending on the level of precision and direction of rounding. For example, to round to the nearest hour, use 1/24; to round to the nearest 15 minutes, use 15/1440; to round to the nearest 10 seconds, use 10/86400. Note that 1440 is the number of minutes in a day, and 86400 is the number of seconds in a day.
- Press the Enter key to get the result. The result will be a decimal number between 0 and 1. For example, the result of =MROUND(A1, 1/24) will be 0.333333333, which represents 8:00:00 AM.
- Apply a time format to the cell that contains the result, to display it as a time value. For example, apply the format hh:mm:ss to cell B1, to display the result as 08:00:00.
Explanation
To illustrate the theory and procedures of rounding time values in Excel, we will use a scenario with real data and show the results in a table. Suppose we have a list of time values in column A, representing the start times of some tasks. We want to round these time values to the nearest hour, minute, or second, using the MROUND, CEILING, and FLOOR functions. We also want to display the results in different time formats, such as hh:mm:ss, h:mm AM/PM, or [h]:mm:ss.
The table below shows the results of applying the formulas and formats to the time values in column A. The formulas are shown in the top row of each column, and the formats are shown in the bottom row of each column. The results are shown in the cells below the formulas and formats.
| Time | =MROUND(A2,1/24) | =CEILING(A2,1/24) | =FLOOR(A2,1/24) | =MROUND(A2,15/1440) | =CEILING(A2,15/1440) | =FLOOR(A2,15/1440) | =MROUND(A2,10/86400) | =CEILING(A2,10/86400) | =FLOOR(A2,10/86400) |
|---|---|---|---|---|---|---|---|---|---|
| hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss | hh:mm:ss |
| 8:29:59 AM | 08:00:00 | 09:00:00 | 08:00:00 | 08:30:00 | 08:30:00 | 08:15:00 | 08:30:00 | 08:30:10 | 08:29:50 |
| 10:15:23 AM | 10:00:00 | 11:00:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:15:00 | 10:15:20 | 10:15:30 | 10:15:20 |
| 12:45:37 PM | 13:00:00 | 13:00:00 | 12:00:00 | 12:45:00 | 13:00:00 | 12:45:00 | 12:45:40 | 12:45:40 | 12:45:30 |
| 2:07:49 PM | 14:00:00 | 15:00:00 | 14:00:00 | 14:00:00 | 14:15:00 | 14:00:00 | 14:07:50 | 14:08:00 | 14:07:40 |
| 4:32:15 PM | 17:00:00 | 17:00:00 | 16:00:00 | 16:30:00 | 16:45:00 | 16:30:00 | 16:32:10 | 16:32:20 | 16:32:10 |
| 6:54:01 PM | 19:00:00 | 19:00:00 | 18:00:00 | 19:00:00 | 19:00:00 | 18:45:00 | 18:54:00 | 18:54:10 | 18:54:00 |
As we can see from the table, the MROUND function rounds the time values to the nearest hour, minute, or second, depending on the multiple specified. The CEILING function rounds the time values up to the next higher hour, minute, or second, depending on the multiple specified. The FLOOR function rounds the time values down to the next lower hour, minute, or second, depending on the multiple specified.
We can also see that the time formats affect how the results are displayed. For example, the format hh:mm:ss shows the hour, minute, and second values with leading zeros. The format h:mm AM/PM shows the hour and minute values with a space and AM or PM indicator.
Alternative Approaches
Besides using the MROUND, CEILING, and FLOOR functions, there are some alternative approaches that we can use to round time values in Excel. These approaches may be useful for different scenarios or preferences.
- Using the ROUND, ROUNDUP, and ROUNDDOWN functions: These functions are similar to the MROUND, CEILING, and FLOOR functions, except that they round the time values to a specified number of decimal places, rather than to a multiple of another number. For example, ROUND(time, 2) rounds the time value to two decimal places, which is equivalent to rounding to the nearest hundredth of a day, or the nearest 14 minutes and 24 seconds. ROUNDUP(time, 2) rounds the time value up to two decimal places, which is equivalent to rounding up to the next higher 14 minutes and 24 seconds. ROUNDDOWN(time, 2) rounds the time value down to two decimal places, which is equivalent to rounding down to the next lower 14 minutes and 24 seconds. We can use these functions to round time values to any level of precision, by adjusting the number of decimal places accordingly. For example, to round time values to the nearest 5 minutes, we can use ROUND(time, 3), where 3 represents three decimal places, or the nearest thousandth of a day, or the nearest 5 minutes and 12 seconds.
- Using the INT and MOD functions: These functions can extract the integer and fractional parts of a number, respectively. For example, INT(0.354166667) returns 0, which is the integer part of 0.354166667, and MOD(0.354166667, 1) returns 0.354166667, which is the fractional part of 0.354166667. We can use these functions to round time values to the nearest hour, minute, or second, by extracting the fractional part and multiplying it by the appropriate factor. For example, to round time values to the nearest hour, we can use INT(time) + MOD(time, 1) * 24, where 24 represents the number of hours in a day. To round time values to the nearest minute, we can use INT(time) + MOD(time, 1) * 1440, where 1440 represents the number of minutes in a day. To round time values to the nearest second, we can use INT(time) + MOD(time, 1) * 86400, where 86400 represents the number of seconds in a day.
- Using the TIME function: This function can create a time value from given hour, minute, and second values. For example, TIME(8, 30, 0) returns 0.354166667, which represents 8:30:00 AM. We can use this function to round time values to the nearest hour, minute, or second, by using the INT, ROUND, ROUNDUP, or ROUNDDOWN functions to round the hour, minute, and second values separately. For example, to round time values to the nearest hour, we can use TIME(ROUND(time * 24, 0), 0, 0), where time * 24 converts the time value to an hour value, and ROUND(time * 24, 0) rounds the hour value to the nearest integer. To round time values to the nearest minute, we can use TIME(INT(time * 24), ROUND(time * 1440, 0) – INT(time * 24) * 60, 0), where time * 1440 converts the time value to a minute value, and ROUND(time * 1440, 0) – INT(time * 24) * 60 rounds the minute value to the nearest integer, after subtracting the hour value multiplied by 60. To round time values to the nearest second, we can use TIME(INT(time * 24), INT(time * 1440) – INT(time * 24) * 60, ROUND(time * 86400, 0) – INT(time * 1440) * 60), where time * 86400 converts the time value to a second value, and ROUND(time * 86400, 0) – INT(time * 1440) * 60 rounds the second value to the nearest integer, after subtracting the minute value multiplied by 60.
