Quarter of the hour formula

[Bluzdog]

I'm trying to find the formula to round the elapsed time of worked hours
to the closest "QUARTER" of the hour. For example:

Cell A1= 7:00
Cell B1= 15:43

The formula I need will result in 8.75 (where .75 represents 45 minutes,
rounded off to the closest quarter of the hour).

Simply subtracting the time doesn't cut it.


Can anyone help?
Thanks in advance

Bluzdog

 

[JohnI in Brisbane]

Bluzdog,

try this formula-

=ROUND((A2-A1)*24*4,0)/4

If you want the time back in Excel Time format use this formula-

=ROUND((A2-A1)*24*4,0)/4/24

and format as time.

Please post again to say how you went. It's good to know whether this
suggested solution worked or required more thought.



Regards,



JohnI

 

[Ron Rosenfeld]

I'm trying to find the formula to round the elapsed time of worked hours
to the closest "QUARTER" of the hour. For example:

Cell A1= 7:00
Cell B1= 15:43

The formula I need will result in 8.75 (where .75 represents 45 minutes,
rounded off to the closest quarter of the hour).

Simply subtracting the time doesn't cut it.


Can anyone help?
Thanks in advance

Bluzdog

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


--ron

 

[JohnI in Brisbane]

Also posted in microsoft.public.excel.worksheet.functions

Post any further suggestions to that group.

regards,

JohnI