Follow-up on rounding question


Posted by Alan Holle on August 24, 2001 1:45 PM

My original posting is shown below:

I received several proposed solutions. The one that worked was: =FLOOR(("7:09 P"-"12:00 P")*24,0.1)

Now, to complicate matters, I have several time segments per day that need to go into this calculation. For example:
12:00 pm to 7:09 pm
7:39 pm to 9:00 pm
9:30 pm to 11:30 pm
How can I modify this formula to include these additional time segments. Thanks for your help.

Posted by Mark W. on August 24, 2001 1:50 PM

Alan, each of the text representation of time values
(e.g., "7:09 P") in the formula, =FLOOR(("7:09 P"-"12:00 P")*24,0.1),
can be replaced with a cell reference. If I'm
understanding your concern you can simply put each
of these time values in a worksheet cell and modify
the suggested FLOOR worksheet function to reference
these cells.

Posted by Alan Holle on August 24, 2001 2:01 PM

Mark- I understand about the cell references but how do I modify the FLOOR function to reference these cell? I tried to do that but apparently I'm doing something wrong. Thanks for your help.

Posted by Aladin Akyurek on August 24, 2001 2:15 PM

The formula I suggested

=IF((A1-INT(A1))<=0.5,FLOOR(A1,1),CEILING(A1,1))

should be applied to time that has already been converted into its decimal form. That is, A1 should contain the converted result. Your first post about the conversion. Mark told you how to solve that. This formula should work too.

Lets say that you have the times in A and B from the first row on:

[1] In C1 enter: =(B1+(B1<A1)-A1)*24 [ C1 must be general formatted. ]

[2] In D1 enter: =IF((C1-INT(C1))<=0.5,FLOOR(C1,1),CEILING(C1,1))

If you can afford computing the same thing more than once, you can substitute ((B1+(B1<A1)-A1)*24) for C1 in [2].

Aladin

============

Posted by Mark W. on August 24, 2001 2:18 PM

The 1st argument is a number which in this case is
the difference of two time values (e.g., ("7:09 P"-"12:00 P"))
converted from fractional parts of a day (which is
how Excel stores time) to hours through multiplication
by 24. You can subsitute any of these paired time
values into this equation either as a text strings as
I have done or by cell references. If you enter 9:00 P
into cell A1 and 6:39 P into cell B1 then...
=FLOOR((A1-B1)*24,0.1) produces 2.3 hours.

Posted by Mark W. on August 24, 2001 2:38 PM

Aladin, your formula uses a significance of 1 rather
than 0.1 for your FLOOR function call. Hence, using
the times 9:00 PM and and 6:39 P, the elapsed time
in hours, 2.35, is reduced to 2 instead of the
desired 2.3 hours. Also, you formula rounds up using
the CEILING worksheet function, yet my understanding
of Alan's request was based on a need to always
round down to the nearst 1/10th of an hour.



Posted by Aladin Akyurek on August 24, 2001 2:45 PM

That's corrigible, although there is no need for that.
I was worried about being misunderstood: that that formula was intended to apply to unconverted times!