Time in 2.0 (for 2:00) erronous display

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

C2 4/7/2004 4:22:00 P
C3 4/7/2004 5:22:00 P
C4 (C3-C2) 01:00 (HH:MM

C5 =CEILING(((C4-INT(C4))*24),0.1) shows 1.

Shouldn't it show 1.0 for a complete hour if it shows 01:00 as the differenc

Please help
 
Hi Frank!

It still returns 1.1

It's a binary notation precision problem where the binary rounding has
produced a result that is just a tad over 1 and is therefore being
rounded up to 1.1

I think that one approach might be:

=CEILING(--(TEXT(C4*24,"#.00000000")),0.1)

But I'm sure that there are others that are probably more elegant.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Frank!

If you do the full entry:

C1:
07-Apr-2004 4:22:00
C2:
07-Apr-2004 5:22:00
C4:
=C2-C1
C5:
=CEILING(C4*24,0.1)
Returns 1.1
But:
=CEILING(--(TEXT(C4*24,"#.00000000")),0.1)
Returns 1

The approach I used was to avoid the binary rounding by using text to
truncate at the eighth decimal place and then use -- to convert back
to a number that I then apply rounding to.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi

Thanx, I'll take your word for it; just inquisitive - what exactly does the formula d

Regard
Sachin
 
Hi Sachin!

Trust me. Never take the word of an Australian!

The formula suggested was:
=CEILING(--(TEXT(C4*24,"#.00000000")),0.1)

=TEXT(C4*24,"#.00000000")

Returns in text form the result of C24*24 rounded to the 8th decimal
place.

I then convert that back to a number using -- which has the same
effect as multiplying by -1 twice. That operation converts any text
number to a number or a Boolean TRUE or FALSE to 1 or 0.

Now I can use CEILING to round up to the nearest 0.1

Never hesitate to ask why or how a solution works as that will give
you a far greater understanding of Excel.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi

To detail it out, the consumer is Billed on TIME BASI
For a facility, a particular charge applies for first hour an
for every 30 minutes part thereof another charge applie

So, 1 hour 1 minutes till 1 hour 30 minutes will be charged 1 hour 30 minutes
1 hour 31 minutes till 2 hours will be charged 2 hour

Specific of part thereof change for other facilitie

Norman's solution solves the matter for the time being; guess MS already know

Regard
Sachin
 
Back
Top