Rounding time to the quarter hour.

[Karl]

Can any one tell me how to write a formula that rounds off
cell A1 to the nearest quarter hour plus it needs to round
off cell B1 to the nearest quarter hour then subtract A1
from B1.
So if Cell A1 = 7:05am and Cell B1 = 10:08am
The answer would be 2:45 Hours and NOT 3:00 Hours.

Thanks for the help!

 

[Felipe]

Karl,

To round up the times, you can use:
=+TIME(HOUR(A1),IF(MINUTE(A1)<7.5,0,IF(MINUTE(A1)
<22.5,15,IF(MINUTE(A1)<37.5,30,IF(MINUTE(A1)
<52.5,45,60)))),0)

You can eather round each time or round the result of the
substraction, as needed. From your example I don't
understand wich is your goal.

Regards,
Felipe

 

[Ron Rosenfeld]

Can any one tell me how to write a formula that rounds off
cell A1 to the nearest quarter hour plus it needs to round
off cell B1 to the nearest quarter hour then subtract A1
from B1.
So if Cell A1 = 7:05am and Cell B1 = 10:08am
The answer would be 2:45 Hours and NOT 3:00 Hours.

Thanks for the help!

Well, if you are doing the rounding first, and then subtracting, your answer
should be 3:15 hours.

7:05 rounded to the nearest quarter is 7:00
10:08 rounded to the nearest quarter is 10:15

10:15 - 7:00 = 3:15


Formula:

=ROUND(B1*1440/15,0)*15/1440-ROUND(A1*1440/15,0)*15/1440


--ron

 

[pfsardella]

Search for 'Rounding Time value to Quarter Hour' on David McRitchie's
WebPage.

http://www.mvps.org/dmcritchie/excel/datetime.htm

See Chip Pearson's WebPage.

http://www.cpearson.com/excel/datearith.htm#RoundingTimes

HTH
Paul