Round To 5 Minute Mark

[mgirvin]

Dear World’s Smartest Excel Team,

I have this formula for rounding up to the five minute mark:

=CEILING(A2,0.00347222222222224)

Where A2 has a time.

Question 1: Is my use of the decimal the best way to do this (best means accurate result and/or shortest formula)?


Also, what formula could I use to apply the standard rounding rule (select digit you want to round to, if the digit to the right it is 5 or more add 1, if the digit to the right it is less than 5 leave as is; standard ROUND function rule) so that I could round to the 5 minute mark?

For example:

2:02:30 PM would become 2:05:00 PM
2:02:29 PM would become 2:00:00 PM

Any ideas?

[Jon von der Heyden]

Hello

Q1:
=CEILING(A2,"00:05")


Q2:
=ROUND(B1/"00:05",0)*"00:05"

[steimel386]

Hey,

Keep in mind there is also ROUNDDOWN and ROUNDUP for those more specific instances.

[mgirvin]

Dear Jon von der Heyden,

That is so clever. I can hardy believe it:

1) Ask how many 5 minute chunks there are, 2) Round to the integer, 3) Then multiply by five minutes!!

Did you make that up, or did you see it somewhere?

Thanks a lot, Jon von der Heyden!

[Jon von der Heyden]

Originally Posted by mgirvin

Did you make that up, or did you see it somewhere?

I think I first saw this in a post by Barry Houdini. This question appears quite frequently, only usually to round to nearest 0.5.

You're welcome!

[mgirvin]

It all starts with Aladin and Houdini, I guess.

We are lucky to be part of an amazing Excel Team!!!

Thanks again!

[barry houdini]

Originally Posted by mgirvin

It all starts with Aladin and Houdini, I guess.

Well, I've used it but I didn't start it........

Another option to round to the nearest 5 minutes would be MROUND, but you need Analysis ToolPak add-in in Excel versions 2003 and earlier, i.e.

=MROUND(A1,"0:05")

[mgirvin]

Dear Houdini,

Another great trick! I like MROUND becasue it completes the group: CEILING, FLOOR, MROUND. Just as ROUNDUP, ROUNDDOWN, ROUND are a group.

Thanks for helping to Lead the Team, Houdini!