Comparing minutes.

[Mike Klick]

I know the below doesn't work but hopefully you'll get the idea.

IF(D423-C423<=0:15,0:15,ROUND((D423-C423)*96,0)/96)

If the value from D423-C423 is 15 minutes or less I want to put 0:15 in a
field. If greater round it to 15 minutes and put it in field.

All the cells have a Custom format of [h]:mm. Converting the 0:15 is my
problem. D423-C423 works fine and place the correct minute in the Custom
cell. But how do I convert 0:15 minutes to something I can use - In both
places.

Thanks,

Mike

 

[Myrna Larson]

=IF(D423-C423<=TIME(0,15,0),TIME(0,15,0),ROUND((D423-C423)*96,0)/96)

or, since a time is stored as a fraction of a day, and there are 24 hours in a
day, 1/24 is equivalent to 1 hour. 1/(24*4) or 1/96 is equivalent to 1 quarter
hour, or 15 minutes. So you can speed up the calculation by specifying 1/96
instead of calling the TIME function to do the division.

=IF(D423-C423<=1/96,1/96,ROUND((D423-C423)*96,0)/96)

BTW, that's where the 96 is coming from in the last part of your formula.

 

[JulieD]

Hi Mike

by "round it to 15 minutes" i'm assuming you mean round it to the nearest
multiple of 15 mins (15, 30, 45, 1)

=MAX(0.010417,ROUNDUP((B1-A1)/"00:15",0)*"00:15")

Cheers
JulieD