ROUNDING TIME TO THE NEAREST QUARTER HOUR

[MARCY]

I am trying to force the result in my formula column to
display as one of only three decimals, .25, .50 or .75.

Time-In Time-Out Hours Worked Desired Display

9:00 AM 4:13 PM 7.22 7.25
10:25 AM 5:16 PM 6.85 6.75

The formula I am using is <=SUM(E2-D2)*24> where E=time-
out and D=time-in.

Anyone have insight about rounding time to the nearest
quarter hour?

Thanks for your help.

 

[Alan]

I am trying to force the result in my formula column to
display as one of only three decimals, .25, .50 or .75.

Time-In Time-Out Hours Worked Desired Display

9:00 AM 4:13 PM 7.22 7.25
10:25 AM 5:16 PM 6.85 6.75

The formula I am using is <=SUM(E2-D2)*24> where E=time-
out and D=time-in.

Anyone have insight about rounding time to the nearest
quarter hour?

Thanks for your help.

Easiest way is probably to multiply your result by four, round to the
nearest whole number, and then divide by four.

To generalise:

To round to the nearest X, you multiply by 1/X, round to the nearest
whole number, then divide by 1/X.

HTH,

Alan.

 

[Biff]

Hi Marcy,

Try these:

=ROUND((B1-A1)*24/0.25,0)*0.25

If the times might roll over past midnight:

=ROUND((B1-A1+(A1>B1))*24/0.25,0)*0.25

Biff