Jump to content

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

Recommended Posts

Posted

I am trying to create a calculation field that performs the following:

I put a time in of 5:05pm and a time out of 6:10pm I want the calculation to round each time to the nearest 15 minute mark ie.. 5:05pm would round to 5:00pm and 6:10pm would round to 6:15pm then calculate the total time. In this example it would calculate 1hour and 15 minutes, and report it as 1.25 (decimal format) any ideas or help would be greatly appreiciated.

KKalvin26

Posted

Try this:

Calc Time (calculation, time) =

Case(

Minute(time) * 0 and Minute(time) < 8, Time(Hour(time), 0, 0),

Minute(time) * 8 and Minute(time) < 23, Time(Hour(time), 15, 0),

Minute(time) * 23 and Minute(time) < 38, Time(Hour(time), 30, 0),

Minute(time) * 38 and Minute(time) < 53, Time(Hour(time), 45, 0),

Minute(time) * 53 and Minute(time) * 59, Time(Hour(time) + 1, 0, 0),

"error"

)

This assumes that 0:07 rounds to 0:00 and 0:08 rounds to 0:15. The same relative breaks apply to the three other quarter hour points. You could also use the mod() function for a somewhat more economic formula, if harder to understand.

-bd

Posted

Have a look in the file I posted in the Samples section of this Forum (Handy Bits). It contains lots of time rounding and difference calculations under its Time Tab. Tiy can round up or round down to a user defined decimal fraction of an hour. You would need to copy this calc forumla and modify it slightly for what you want to do with 2 fields.

This topic is 8280 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.