Hour() function

[Guest]

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

 

[Peo Sjoblom]

One way

=INT(A1*24)

assuming the 25:28 is in A1

Regards,

Peo Sjoblom

 

[Guest]

Nice job Peo. Thanks.

 

[Guest]

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.

 

[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