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.

Tom’s Tutorials For Excel: Rounding Elapsed Time by 5 Minutes

Here are 3 formulas that round an elapsed time by the 5-minute mark, in terms of nearest 5-minute mark, up to the next 5-minute mark, and down to the last (lower) 5-minute mark.

In the picture, notice the differences for each employee’s elapsed time in decimalized 5-minute segments, depending on if, and how, you decide to round or not round. The number 288 in the formulas relates to the count of 5-minute segements that comprise a full calendar day.


The formula that subtracts the raw difference, entered in cell D3 and copied down:
=(C3-B3)*288

The subtraction formula rounding to the nearest 5-minute mark, entered in cell E3 and copied down:
=ROUND((C3-B3)*288,0)

The subtraction formula rounding up to the next 5-minute mark, entered in cell F3 and copied down:
=CEILING((C3-B3)/(1/288),1)

The subtraction formula rounding down to the prior 5-minute mark, entered in cell G3 copied down:
=FLOOR((C3-B3)/(1/288),1)

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Quarter-Hour

Here are 3 formulas that round an elapsed time by the quarter-hour, in terms of nearest quarter-hour, up to the next quarter-hour, and down to the last (lower) quarter-hour.

In the picture, notice the differences for each employee’s elapsed time in decimalized quarter-hour segments, depending on if, and how, you decide to round or not round. The number 96 in the formulas relates to the count of quarter-hour (15-minute) segements in a full calendar day.


The formula that subtracts the raw difference, entered in cell D3 and copied down:
=(C3-B3)*96

The subtraction formula rounding to the nearest quarter-hour, entered in cell E3 copied down:
=ROUND((C3-B3)*96,0)

The subtraction formula rounding up to the next quarter-hour, entered in cell F3 copied down:
=CEILING((C3-B3)/(1/96),1)

The subtraction formula rounding down to the prior quarter-hour, entered in cell G3 copied down:
=FLOOR((C3-B3)/(1/96),1)

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Half-Hour

Here are 3 formulas that round an elapsed time by the half-hour, in terms of nearest half-hour, up to the next half-hour, and down to the last (lower) half-hour.

In the picture, notice the differences for each employee’s elapsed time in decimalized half-hour segments, depending on if, and how, you decide to round or not round. The number 48 in the formulas relates to the count of half-hour (30-minute) segements that comprise a full calendar day.


The formula that subtracts the raw difference, entered in cell D3 and copied down:
=(C3-B3)*48

The subtraction formula that rounds to the nearest half-hour, entered in cell E3 and copied down:
=ROUND((C3-B3)*48,0)

The subtraction formula that rounds up to the next half-hour, entered in cell F3 and copied down:
=CEILING((C3-B3)/(1/48),1)

The subtraction formula rounding down to the prior half-hour, entered in cell G3 and copied down:
=FLOOR((C3-B3)/(1/48),1)

Tom’s Tutorials For Excel: Rounding Times of Day by 5 Minutes

Here are three pictures to show how a time of day can be rounded…
• To its nearest 5-minute mark.
• Up, to its next 5-minute mark.
• Down, to its last 5-minute mark.

Picture #1 – Rounding a time to its nearest 5-minute mark.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=ROUND(B3*288,0)/288


Picture #2 – Rounding a time up to its next 5-minute mark.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=CEILING(B3,0.5/144)


Picture #3 – Rounding a time down to its last 5-minute mark.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=FLOOR(B3,0.5/144)

Tom’s Tutorials For Excel: Rounding Times of Day by the Quarter Hour

Here are three pictures to show how a time of day can be rounded…
• To its nearest quarter-hour.
• Up, to its next quarter-hour.
• Down, to its last quarter-hour.

Picture #1 – Rounding a time to its nearest quarter-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=ROUND(B3*96,0)/96


Picture #2 – Rounding a time up to its next quarter-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=CEILING(B3,0.5/48)


Picture #3 – Rounding a time down to its last quarter-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=FLOOR(B3,0.5/48)

Tom’s Tutorials For Excel: Rounding Times of Day by the Half Hour

Here are three pictures to show how a time of day can be rounded…
• To its nearest half-hour.
• Up, to its next half-hour.
• Down, to its last half-hour.

Picture #1 – Rounding a time to its nearest half-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=ROUND(B3*48,0)/48


Picture #2 – Rounding a time up to its next half-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=CEILING(B3,0.5/24)


Picture #3 – Rounding a time down to its last half-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=FLOOR(B3,0.5/24)

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Hour

Here are 3 formulas that round an elapsed time by the hour, in terms of nearest hour, up to the next hour, and down to the last (lower) hour.

In the picture, notice the differences for each employee’s elapsed time in decimalized hours, depending on if, and how, you decide to round or not round. The number 24 in the formulas relates to the count of hours that comprise a full calendar day.

The formula that subtracts the raw difference, entered in cell D3 and copied down:
=(C3-B3)*24

The subtraction formula that rounds to the nearest hour, entered in cell E3 and copied down:
=ROUND((C3-B3)*24,0)

The subtraction formula that rounds up to the next hour, entered in cell F3 and copied down:
=CEILING((C3-B3)/(1/24),1)

The subtraction formula that rounds down to the prior hour, entered in cell G3 and copied down:
=FLOOR((C3-B3)/(1/24),1)

Tom’s Tutorials For Excel: Separating Date and Time From NOW.

Here’s how you can store the date in one cell, and the time in another cell, of a source cell that contains both a date and time, such as if that source cell were holding the NOW function.

This is different than just copying the NOW function into 2 other cells and formatting those 2 other cells as date and time respectively. Formatting a cell only changes the appearance of its contents, but the underlying value in that scenario would still be both a date and time in all 3 cells.

Here, you are separating out the actual date value, and the actual time value, with this result:

Here are the steps by example.

Step 1
Start by entering the NOW function as you see in cell A2.

Step 2
Maybe you’ll want your NOW function to constantly update itself. But just for fun, if you want to make it constant (unchanging), select the cell, press F2, then press F9, then press Enter.

Here is cell A2, selected with its constant date and time value seen in the formula bar:

Step 3
• Select the cell (B2 in this example) that will hold the date.
• In that cell, enter the formula =INT(A2).
• With that cell selected, right click it and select Format Cells.

Step 4
In the Format Cells dialog…
• Click onto the Number tab.
• In the Category pane, select Date.
• In the Type list, select your desired Date format.
• Click OK.

Step 5
• Select the cell (C2 in this example) that will hold the time.
• In that cell, enter the formula =MOD(A2,1).
• With that cell selected, right click it and select Format Cells.

Step 6
In the Format Cells dialog…
• Click onto the Number tab.
• In the Category pane, select Time.
• In the Type list, select your desired Time format.
• Click OK.