Hour() function

S

Steven

I am calculating overtime and have a time of 25:28. That is 25 hours and 28
minutes. It is formatted Custom [h]:mm so you actually see the 25:28 in the
cell. I want to pull the hours into a sererate cell as 25 in this case. I
keep getting a 1 and if I try to add this hour and another number it gives me
another confusing answer.

My question is how do I return the hours (25 in this case) to its own cell
and have it actually be a value of 25.

Thank you for your help.

Steven
 
S

Sloth

I know the question has been answered already, but I thought I would put my
two cents in. 1st: Your problem is with your understanding of the hour()
function. It returns 0-23 depending on what time of a day it is. It is
supposed to be used with extracting the time of day from a date. 2nd: when
times are entered into a cell, they are not stored as a time (not they way
you think of it anyway). They are stored as days (to be more exact the
number of days from 1/0/1900, if no date is accompanying the time). So in
your example 25:28 is really 1.0611 days (or 25.4666 hours, or even 1/1/1900
1:28 AM). You can see this by formatting it as general (or to see the date:
m/d/yyyy h:mm AM/PM). So in Peo's answer you are simply converting days to
hours and dropping the fraction. If you think in these terms, it might help
you in the future with some other problems.
 
R

Robert_Steel

An alternative aproach
one way to extract the hours is using the TEXT function

=--TEXT(A1,"[h]")
just using the double unarry to coerce the text to numeric

I am not sure if this is slower than the =INT(A1*24) but could be more
accessible to the less knowledgable

cheers RES
 
Top