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

 

[Corey872]

Maybe the long way around, but it's the first solution that comes to
me...

1. Set up cells formatted for time and do the subtraction to get your
8:43.

2. Use the 'Date & Time', HOUR function to extract the 8 to another
cell and use the MINUTE function to extract the 43 to a third.

3. Set up a nested IF THEN statement from the 'Logical' group of
functions so that:

IF minute cell >=0, and <7.5 THEN new cell = 0
IF minute cell >=7.5 , <=22.5 THEN new cell = .25
IF minute cell >22.5 , <=30 THEN new cell = .5
....etc.

Or, adjust the >, < numbers and logic where ever you want to round to
based on minutes worked. Just make sure you make use of good >= or <=
statements so all times are covered. It may not hurt to make the
"false" side something like -1000, you you would immediately see any #
that didn't get sorted into some category. Also note that you could
even make uneven breaks in the "rounding"...make it harder (or easier)
to get an extra 1/4 hour as the time increased, for instance.
Either way, you now have a logic statement that breaks 0-59 into 0,
..25, .5, .75, and 1 increments.


4. Finally, add your HOUR cell which should contain an 8 to your new
'LOGIC' cell which should contain a .75 (based on your 43 minute
example) and you would have a new cell with 8.75.

Hope this helps,

Corey

 

[Gerrit]

Round(anser/0.25,0)*0.25
where anser is the difference between the times.

 

[GB]

Not quite right Gerrit - at least on my version of Excel.

I find that you need this
= Round((A1-B1)*24*4,0)/24/4