Rounding Time Calculations in Excel
When performing time calculations in Excel you may need to round the result to a particular time frame such as the nearest hour, half hour or 15 minutes.
This can easily be done with a little knowledge of how to use the time in Excel and some simple formulas.
Calculating the Time Difference
In the example below we may wish to calculate the time difference between the date and time in cell A2 and the date and time in cell B2.
Calculating the time difference is achieved by simply entering the formula =B2-A2 (B2 is entered first as it is the later time).
The result of the formula will appear as 0.228275 unless it is formatted in time format. After formatting the result should appear as 5:28:43.
Round the Time to a Significant Multiple
To round the time to a multiple of significance, the MROUND function will be used. If you have round numbers in Excel before you may be familiar with some of the round functions on offer. The MROUND function will allow us to enter the multiple we want to round to.
Round to the Nearest Hour
To round to the nearest hour, enter the formula below.
The result is round down to 5:00. The formula to calculate the time difference is integrated into the function, and the “1:00” is used to specify that we want the result rounded to the nearest hour.
Round to the Nearest 15 Minutes
To round the result to the nearest multiple of minutes, use the formula below.
“0:15” is entered to specify that we want the result to the closest multiple of 15 minutes.