PDA

View Full Version : Round toTime to Quarter of the Hour



brorick
09-05-2006, 03:01 PM
Hello. I am trying to use the following formula to round up to the quarter of the hour. I would prefer that when the total time is 2:11 that the cell will round up to 2:15 and the cell is formated to convert 2:15 to 2.25. I hope this makes sense. If the time is 2:06 then the cell is converted to 2.00.

'Time is calculated in this cell based on cells c13:f13.
r13 =(C13>D13)+D13-C13+(E13>F13)+F13-E13

'This cell is supposed to round up to the closest quarter of the hour.
t13=ROUND(R13*24/0.25,2)*((0.25/24)*24)

At first i seem to get it to work but then it doesn't. :banghead: Anyone's help is appreciated. Thank you.

Cyberdude
09-05-2006, 07:43 PM
I don't have enough info to test your formulas, but look at this part:
t13=ROUND(R13*24/0.25,2)*((0.25/24)*24) I'm concerned that the preference rules might be giving you trouble with the red operators. In other words, is it supposed to be:
(R13 * 24) / 0.25
or should it be:
R13 * (24 / 0.25)
Just wondering.

mdmackillop
09-06-2006, 12:06 AM
Try
=ROUND(R13*24/0.25,0)/24*0.25

Bob Phillips
09-06-2006, 02:40 AM
=ROUNDUP(R13*96,0)/4

and format as general