Rounding Time

Thank you! I will try these formulas!

Thanks Michael.

@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

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

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

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.

@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

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

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

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

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.

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

@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