Three tips for rounding Excel time values

Date arithmetic is an Excel fundamental and very powerful. Excel does a lot internally, so you won’t always need a special function to add and subtract date and time values. You can enter the values and use a simple expression.  For instance, the simple timesheet below uses a simple expression - ($C2-$B2 + ($B2>$C2))-$D2 - to add the hours worked (elapsed time) each day.

What this simple expression lacks is a bit of rounding. Most payrolls work with specific intervals and not real time. For example, if you work any part of a quarter hour, they pay you for the full quarter hour. You could train your users to round their input, but that might not be practical. Fortunately, there are other options. (The expression cells use the custom time format, h:mm.)

Automatically sign up for TechRepublic's Windows and Office newsletter!

Option 1: MROUND()

Rather than rely on users, consider using Excel’s MROUND() function to round elapsed time to specific intervals. This function returns a value rounded to a specified multiple, using the following syntax:

MROUND(value,multiple)

This function rounds up, away from zero, if the remainder of dividing value by multiple is greater than or equal to half the value of multiple. This behavior will eliminate MROUND() if you always want to round up. To illustrate using the simple timesheet shown above, do the following:

1. Enter the specific interval, in minutes, in F2. In this case, enter 15 to represent the quarter-hour interval.
2. In G2, enter the following formula
   =TIME(HOUR(E2),MROUND(MINUTE(E2),$F$2),0)
3. Copy the formula to the remaining cells. If you don’t get the right results, make sure the F2 component is an absolute reference.

It works great, but it doesn’t give you the results you need, every time. The formula rounds the elapsed time for 3/20 down, not up. If you always want to round up, try the next option.

Note: In the menu versions of Excel, you’ll need to install MROUND() from the Analysis Toolpak.

Option 2: CEILING()

Excel’s CEILING() function provides a second rounding option, whereby the function always rounds up to a specific interval. To illustrate its use, enter the following formula in H2 and copy it:

=TIME(HOUR(E2),CEILING(MINUTE(E2),$F$2),0)

This time, the elapsed time for 3/20 rounds up, like all the others.

Option 3: FLOOR()

When dealing with payrolls, you’ll usually want to force the rounding up, but there is one more option - you might want to round down. You can do so by using the FLOOR() function.  To illustrate this function, enter the following formula in G2 and copy it:

=TIME(HOUR(E2),FLOOR(MINUTE(E2),$F$2),0)

This formula rounds all of the elapsed time values down to the nearest quarter-hour interval.

Worth mentioning

You can quickly update any of the rounding formulas by changing the minute interval in F2 (the most common will be 15 and 30). Just remember that you’re dealing with the minute value. You can apply this technique to the hour value as well, but it’s not likely you’d do so in a payroll scenario. That’s why I concentrated on the minute component.

Example Excel worksheets for this tip are also available.