Rounding Time

[jnmorrison]

How is the best way to round time? I need to round time to the nearest 1/10 hour and the nearest 1/4 hour. Can anyone suggest a good way to do this?

[spb]

Here's one way. Make a calculation field (result text) with the following calculation. This calc assumes that the field holding the actual time is named "test", and that this field is defined as a time field:

Case(

Right(Substitute(TimeToText(test), ":", ""), 4) < "0731", Left(Substitute(TimeToText(test), ":", ""), 2),

Right(Substitute(TimeToText(test), ":", ""), 4) > "0730" and Right(Substitute(TimeToText(test), ":", ""), 4) < "2231", Left(Substitute(TimeToText(test), ":", ""), 2) & ".25" ,

Right(Substitute(TimeToText(test), ":", ""), 4) > "2200" and Right(Substitute(TimeToText(test), ":", ""), 4) < "3731", Left(Substitute(TimeToText(test), ":", ""), 2) & ".5" ,

Right(Substitute(TimeToText(test), ":", ""), 4) > "3730" and Right(Substitute(TimeToText(test), ":", ""), 4) < "5231", Left(Substitute(TimeToText(test), ":", ""), 2) & ".75" ,

Left(Substitute(TimeToText(test), ":", ""), 2) + 1)

This is just for the neast 1/4 situation. You can extend the logic and make another calc field for the tenths. Note that the final default Case assumes that the time is, say, 12:52:30 or greater, so it will take the hour and add one, rounding it to 13. As the result is text, you can add a & ".00" to make it 13.00 if needed.

If you need the result to be a number for further calculation. Imbed the whole thing into a TextToNum function.

Hopefully, someone else will come up with a more elegant solution!

Steve Brown

[BobWeaver]

Bearing in mind that time is stored as the number of seconds past midnight, you can use this info along with the Round function. 1/10th of an hour is 360 seconds, and 1/4th of an hour is 900 seconds.

NearestQuarter = Round(Time/900,0)*900

NearestTenth = Round(Time/360,0)*360