John Meyer wrote:
...
I am trying to extract the hour from a cell and put it into a second
column, but when I use the HOUR() function all it returns is 12:00:00 AM
This is a little tricky.
Times in a spreadsheet are stored internally as fractions of one day. So
all times, and all the times in your first column, are actually
represented by values in the range [0,1).
A date is stored as an integer number of days from some arbitrary
starting date.
Now, when you used B1: =HOUR(A1), the formula returns the hours portion
of the time value: "05:45:49 AM" --> 5.00 (hours). But look: the result
is not in the range for a valid time; the HOUR function does not return
a time value at all, but a simple number of hours.
Strangely, Calc chooses to display the result as if it were a time
instead of just a normal decimal value. Since the result (an integer)
will always have zero for the fractional part, when displayed as a time,
the result will always be "12:00:00 AM". The integer part (5) is ignored.
So, you can either manually change the number format in the second
column to display decimal numbers, or you can use a custom format to
display the number as a time, something like 0":00"
Alternatively, you can simply copy the value from the first column, and
use a custom time format to show only the hours; something like HH":00"
or [HH]":00"
Note that if you do that, the value in those cells will be different
than what is displayed. If you need to do calculations with those
values, you'll need to remember they are time values, not simply hours.
<Joe
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]