Jump to content

Rounding Time


This topic is 7334 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 7334 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.