EXCEL - ROUNDING TIME 2

[susejdrol]

Team,

Does anyone know of a way to round off time. In other words, 8:20 would round down to 8:15, 8:25 would round up to 8:30, etc. I know that you can use the round function to round numbers to whatever number of places you care to go, but I cannot get it to work with time.

Any ideas?

Thanks

CP

 

[Zathras]

Assuming your time is in A1, this formula should provide what you want:
[blue]

  =FLOOR(A1+0.005208333,0.010416666)

[/color]

 

[susejdrol]

That worked perfect. THanks for the help.

CP

 

[TonyJollans]

Zathras,

I'm quite happy with that code but it's not the most obvious for others who might read the thread! I think an explanation is in order.

[ul]Time is held internally as a value between 0 and 1 representing a part of a day. This allows arithmetic and other operations to be performed on it but it is not much use for normal mortals so there are functions to convert from the internal format to external formats.

To use the example given, 0.005208333 represents 7 and a half minutes, and 0.010416666 represents 15 minutes. They could be written, respectively, as Timevalue("00:07:30&quot and Timevalue("00:15&quot, TimeValue being the function to convert hours, minutes, and seconds to internal format.

The various ROUNDing functions do not give flexibility in what they round to - they are standard mathematical functions rounding to decimal places. FLOOR (and CEILING) allow the significance to be specified, so a manual rounding operation is performed by adding half of 15 minutes to the time and then falling back to the earlier exact quarter hour.[/ul]Apologies to all if I'm the only dummy here and this is teaching grandmothers to suck eggs.

Enjoy,
Tony

 

[xlbo]

Not at all Tony - very useful explanation for the archives - star for u

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[susejdrol]

I just wanted to add a little point to the this that I discovered after using that tip.

It worked perfectly from a visual standpoint (i.e. 8:17 became 8:15 just as expected). However, when you do a comparision (i.e. if(A1=A2, ...)) it fails because it loses some decimal places in rounding.

The workaround that I came up with is to apply the formula above to the value that you are comparing as well. Or, as I did in my case, apply the formula across the board and then do a paste special of just values. Then, your comparison formulas (in particular = and <>) will work just fine where they would have failed previously.

Great tip!! Just wanted to fill in the blanks.

CP