Tom’s Tutorials For Excel: Rounding Times by the Minute
Here are 3 formulas that round a time to its nearest minute, or up to the next minute, or down to the previous minute. This example uses the ten best times of the 2012 Boston Marathon. You’ll notice the number 1440 in the first formula, because 1440 minutes comprise a 24-hour day.
The first step is to enter your formula(s), starting with rounding the marathon times in column B to their nearest minute. That formula in cell C3 and copied down is
The next formula rounds up the marathon times in column B to their next minute. That formula in cell D3 and copied down is
The last formula rounds down the marathon times in column B to their lower previous minute. That formula in cell E3 and copied down is
After entering your rounding formulas, their returned values might look like a three-digit number. All that’s needed is to format those cells to make them look like the familiar, intuitive rounded results that you see in the first picture of this lesson.
Select the range of cells where your rounding formulas are. Right-click the selected range, and from the pop-up menu select Format Cells.
In the Format Cells dialog box:
• Click onto the Number tab.
• In the Category pane, select Custom.
• In the Type field, enter the custom format
• Click the OK button.