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
=ROUND($B3*1440,0)/1440
The next formula rounds up the marathon times in column B to their next minute. That formula in cell D3 and copied down is
=CEILING($B3,"00:01:00")
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
=FLOOR($B3,"00:01:00")
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 hh:mm:ss
• Click the OK button.
How do i convert minutes/hours in hh:mm:ss with the help of a excel formula.. Thanks in advance
Thanks for following my Excel blog page. Not sure I understand your question. If for example you manually enter into a cell 6:45 meaning 6 hours and 45 minutes, there is no inference to a seconds time element so it would be :00. If you meant by 6:45 6 minutes and 45 seconds, you should be able to use the format as pictured above. If you post back with a specific example for what is exactly in a cell and what your desired result would be for a formula (or format) for that value, I’d be happy to help answer your question.
Thank You