ROUNDING TIME TO THE NEAREST QUARTER HOUR

  • Thread starter Thread starter MARCY
  • Start date Start date
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.
 
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.
 
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
 
Back
Top