Software optimize

Three tips for rounding Excel time values

Summing Excel date and time values is easy; rounding requires a bit of function help.

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.)

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

20 comments
johnmcdermott
johnmcdermott

Many thanks for an excellent article and for the excellent replies to my query. i especially thank Yogi for the tutorial on beating the "24 hour " clock calculation.

Ray Baker
Ray Baker

For some companies you need to round the input values first. For example my company would take the in/out times of 8:08a and 3:06p and round them to 8:15 and 3:00 first and credit you 6:45 hours. Whereas the formulas above would calculate (6:58) a rounded value of 7:00 hours. Thanks for the tips! I learned new functions to work with time.

lmarks
lmarks

Susan, Why did you insert an extra column (F) and these two steps 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) When you could have simply put the value 15 into the formula like this? =TIME(HOUR(E2),MROUND(MINUTE(E2),15),0)

aevans196204
aevans196204

Two functions shown I was not aware - time calculations often cause issue, so tip on how to handle crossing over midnight also useful.

metyogi
metyogi

Hi Simon, To elaborate the same we need to understand the numerical equivalant of TRUE & FALSE. In Excel TRUE represents Number 1, & FALSE represents number 0. Thus if the difference between 2 timings are negative ($B2>$C2) gives result as TRUE, i.e. 1 and gets added in the negative value, as this negative value is always less than 1, the entire result turns to a positive value. This means 8:00 AM - 10:00 PM gives value as -0.583333333 (in general format / Julian number format). This turns to (-0.583333333 + 1 = 0.416666667) means 10:00 Hrs. Regards, Yogi

Smilodon
Smilodon

These have been a series of great articles. I have a different issue where I need to remove the time value from the date. The most straightforward way I have found to remove the time stamp from the date is the int() function to reference the field with the date time stamp (=int(d2), then copy/paste special/value back in the original field remove the time.

simon.freeman
simon.freeman

I'd appreciate a more detailed explanation as to how this works - if you copy it into a cell as a formula (with and = first) you get true or false but I don't understand how it works in the formula. Thanks.

trs789
trs789

Can anyone give me any other examples of when you'd use these functions other than timekeeping? Thanks.

trs789
trs789

What is the format for Option 1 (F2)? Thanks.

rsdance
rsdance

Another excellent article. I have learned a lot from your posts and really appreciate them. Rupert

ssharkins
ssharkins

Entering the time interval into the sheet and referring to that cell allows for more flexibility in the process. When someone wants to change the interval, you won't have to change the formula, just enter the new interval. You might think it won't change and you might think changing the one formula is no big deal, but that's because the example is simple. When applying these rounding formulas and functions to real apps, things usually get more difficult and less dependable. As sure as someone says "That'll never change!" -- it'll change.

ssharkins
ssharkins

The article was really about rounding, but I'd glad I pulled this old example sheet out of my grab-bag -- it's just a bit of icing that so many are finding the elapsed time formula useful! Thanks for letting me know!

ssharkins
ssharkins

Although the focus of the article is rounding time, you could easily use the rounding functions discussed in any formula that required rounding. Knowing the three different functions should prove useful, even if you don't need the timing aspect -- hope so anyway!

ssharkins
ssharkins

Thank you Rupert -- I'm glad you're finding the Office posts helpful!

CNSHAW
CNSHAW

The ($B2>$C2) section evaluates a logical expression (TRUE or FALSE) which equates to 1 or 0 when combined with the rest of the numerical expression. If you add zero to any logical expression (e.g. =(A1>A2)+0) it will evaluate to one or zero. Thus, this is included to accommodate times crossing midnight as the start time will be greater than the finish time and result in a negative number in the expression. A value of one (24 hours in Excel time format) is therefore added to the remainder of the expression to recognize the finish time is "next day" and produce a positive outcome for displaying the time.

mckinnej
mckinnej

I believe that's for when you cross midnight. Take out the +($B2>$C2) parts and watch what happens to the 3/19 values. (Spoiler: It blows up.)

ssharkins
ssharkins

It's a simple trick that allows one simple formula to handle time values that don't fall within the same 24-hour day.

DaveUnger
DaveUnger

Thanks for showing me that trick Susan. Great article as always.