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

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

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.