Rounding Time Calculations in Excel

When performing time calculations in Excel you may need to round the result to a particular time frame such as the nearest hour, half hour or 15 minutes.

This can easily be done with a little knowledge of how to use the time in Excel and some simple formulas.

Calculating the Time Difference

In the example below we may wish to calculate the time difference between the date and time in cell A2 and the date and time in cell B2.

Rounding time calculations in Excel
Rounding time calculations in Excel

Calculating the time difference is achieved by simply entering the formula =B2-A2 (B2 is entered first as it is the later time).

The result of the formula will appear as 0.228275 unless it is formatted in time format. After formatting the result should appear as 5:28:43.

Round the Time to a Significant Multiple

To round the time to a multiple of significance, the MROUND function will be used. If you have round numbers in Excel before you may be familiar with some of the round functions on offer. The MROUND function will allow us to enter the multiple we want to round to.

Round to the Nearest Hour

To round to the nearest hour, enter the formula below.

=MROUND(B2-A2, “1:00”)

The result is round down to 5:00. The formula to calculate the time difference is integrated into the function, and the “1:00” is used to specify that we want the result rounded to the nearest hour.

Round to the Nearest 15 Minutes

To round the result to the nearest multiple of minutes, use the formula below.

=MROUND(B2-A2, “0:15”)

“0:15” is entered to specify that we want the result to the closest multiple of 15 minutes.

More by this Author

  • Sort Pictures in an Excel List
    0

    If you are using pictures in your Excel spreadsheet you may be having some difficulty with sorting the pictures along with the other data in the list. The good news is that you can sort pictures in an Excel list just...

  • Create an Interactive Excel Chart using Option Buttons
    6

    Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...

  • Brock Lesnar Nutrition and Workouts
    2

    Brock Lesnar is a phenomenon of a man and an athlete. Brock Lesnar weighs a lean 265 lbs and yet possesses extreme speed, agility and athleticism. How a man carrying so much muscle mass can maintain such high levels of...


No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working