ROUNDING TIME TO THE NEAREST QUARTER HOUR

M

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.
 
A

Alan

MARCY said:
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.
Click to expand...

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.
 
B

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
 
Top