The left function does not work when displaying times, how is thi.

[Guest]

The left function does not work when displaying times on Excel 2000. I
entered the data in the following format "9:40," but the data box converts it
to "9:40.00 AM." I changed the format so the time appears as "9:40" on the
spreadsheet. However, I want to display just the hour, the first digit, in
the next column. When I use the left function (=LEFT(B4, 1)), I always get a
zero. Just to see what happened, I displayed the left 4 digits and I receive
some unusual fractions "0.402..." I would greatly appreciate some help on
this topic. Thanks!

 

[Bob Phillips]

=INT(A1*24)

should do it

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tim C]

Other options:

=HOUR(B4)

=B4
and use the custom format h

Tim C

 

[Arvi Laanemets]

Hi

=HOUR(B4)

Or
=B4
and format as custom "h"

In excel, date and time are just numbers. Dates are integer numbers, the
count of days starting from 1 January 1900. Time is decimal part of this
number, i.e. 1 matches for 24 hours. So the time interval 1 hour is really a
number, equal to 1/24.
Because date and time are both numbers, you can easily use datetime values
in Excel (formatted p.e. as "dd.mm.yyyy hh:mm" or "yyyy/mm/dd hh:mm:ss"
etc.), and adding/substaracting time and date values is a simple arithmetic.
When you operate only with time values, you can ignore date (integer) part
(so long the time intervals remain <24 hours), but really you are using
times from 31. December 1899 (the date 0.1.1900).


Arvi Laanemets