Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Rounding Time.

Rounding Time

by Allen Wyatt
(last updated September 26, 2015)

12

There may be instances when you need to round a time value. For instance, you may need to round some time to the nearest quarter-hour. One way to do this is to use the MROUND worksheet function.

For example, let's assume the unrounded time was in cell B7. You could then use the following formula to perform the rounding:

=MROUND(B7, TIME(0,15,0))

This formula relies, as well, on the use of the TIME worksheet function, which returns a time value (in this case, for 15 minutes).

If you don't want to use the MROUND function for some reason, there is another way you can round to the nearest 15 minutes. The clue is to remember that 15 minutes is 1/96th of a day. So to round to the nearest 15 minutes, take the time value, multiply it by 96, round it, and then divide it by 96.

For example, if the time value you wish to round is in cell E5, the following formula does the rounding very nicely:

=ROUND(E5*96,0)/96

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11401) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Rounding Time.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Counting Records Matching Multiple Criteria

Excel provides worksheet functions that make it easy to count things. What if you want to count records that match more than ...

Discover More

Counting with PivotTables

One of the ways you can use PivotTables is to generate counts of various items in a data table. This is a great technique if ...

Discover More

The Changing Relationship of WordArt and Text Boxes

Two of the long-time features in Word are text boxes and WordArt. You might not think these two are related, but they are ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Using the MROUND Worksheet Function

If you want to round a value to some multiple of a whole number, you'll want to become familiar with the MROUND function. ...

Discover More

Rounding to Even and Odd Values

Want to round values so they are always even or odd? You can do it quickly and easily by using the EVEN and ODD worksheet ...

Discover More

Rounding to the Nearest $50

When preparing financial reports, it may make your data easier to understand if you round it to the nearest multiple, such as ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 8?

2015-11-12 08:25:50

Sheila

Thank you! I will try these formulas!


2015-11-11 20:53:06

Alex B

Thanks Michael.


2015-11-11 05:28:48

Michael (Micky) Avidan

@Alex B,
Your solution works fine - however, I usually use a helper-table to allow the user to alter the values whenever is needed (different rounding) which is difficult while hard coded inside the formula (and impossible in your suggested formula).
If Sheila need the result in "Decimal time" (I, personally hate using "Decimal time") she can multiple my suggested formula by 24.
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL


2015-11-10 19:09:11

Alex B


I should have done this the first time but the formula reduces down to:-

=HOUR(D22)+0.25*ROUND(((MINUTE(D22)+2)/15),0)


2015-11-10 18:42:42

Alex B

Hello Sheila,

Michael's lookup solution looks quite good.

Assuming you want to return hours and time in a decimal format, you could try this.

=HOUR(D22)+0.25*ROUND(((MINUTE(D22)+2)/60)/(15/60),0)

Where D22 was the time ie 8:06
Normal rounding would actually be at 8 mins (not 7) so I have added 2 to the mins. I then worked this out as a fraction of 15 mins, rounded it to give the number of quarters and then multiplied that by 0.25

It seems to work.


2015-11-10 07:27:03

Michael (Micky) Avidan

@Sheila,
Try to adopt the suggested solution from the linked picture:

http://screenpresso.com/=3RLpb

--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL


2015-11-09 16:29:36

Sheila

COMPLICATED QUESTION HERE. COMPANY USES MILITARY TIME FOR THE TIME CLOCK BUT ROUNDS AT 6 MINUTES LATE TO THE NEXT QUARTER HOUR. NOT 7 MINUTES. FOR EXAMPLE EMPLOYEE PUNCHES ON AT 0806AM IT IS ROUNDED TO 8:15 AM IN MILITARY TIME THAT WOULD BE 8.25. I need a rounding formula that will round up to the next quarter hour at 6 minutes, 21 minutes, 36 minutes and 51 minutes.
6-20 minutes = 25
21-35 minutes - 50
36-50 minutes = 75
51-59 minutes = 0
0 minutes - 0


2015-09-28 08:42:27

AC

Thanks Alex and Steve, for your replies. Seems I overlooked the most simple fix, which is just to format the cells to > yyyy. That should be sufficient in my case.

Much appreciated.
AC


2015-09-27 15:07:09

Steve Adams

AC asks: "My question it...how would I round a day/month/year date to just the year? I have records that I only want to have shown with their year."
---
As stated this appears you want to have the year that corresponds to the date. If you only want the year to display, you can do that with a custom number format of "yyyy" without the quote marks.

If you need the year only for filtering or for functions, you can use either the TEXT function or the YEAR function "=TEXT(A1,"yyyy")" [returns text value] or "=YEAR(A1)[returns number value] (assuming the date is in cell A1).

If you truly want to round the date to the nearest year use: =IF(MONTH(A1)>6,YEAR(A1)+1,YEAR(A1)) [6/30/2015 rounds to year 2015 and 7/01/2015 rounds to year 2016).

Be sure that the cell is formatted appropriately. If the cell is formatted as a date, the value for a year only will not format as expected (e.g., year 2015 formatted as a date will show "7/7/1905."


2015-09-27 04:48:59

Alex B

Hello AC,

You might need to elaborate on what you are trying to do, based on what you have written just using =Year(b5) where b5 is the date will give you the year.


2015-09-27 00:24:43

AC

Hell and thanks for the great tips. They're always helpful. My question it...how would I round a day/month/year date to just the year? I have records that I only want to have shown with their year.

I suppose I could filter for the year numeral within the date cell and then create another column field to contain just the year numeral.

Thanks for your help!

AC


2015-09-26 08:09:28

Michael (Micky) Avidan

@To whom it may concern,
The following shorter formula will also round the time, in cell B7, to the nearest quarter-hour:
=MROUND(B7,"0:15")
------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.