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]

Reply via email to