Hour() function
 

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

One way

=INT(A1*24)

assuming the 25:28 is in A1

Regards,

Peo Sjoblom


"Steven" wrote in message
...
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

Nice job Peo. Thanks.

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.

"Steven" wrote:

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

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