Extract hour value from time format

[GOSPEL - MAC]

I have one column full of time values. (example: 139:45:34, 165:45:13, etc).
I would like to create a column to the left of my time colum and display just
the hour values (example: 139, 165, etc). How?

And, since I'm asking, is there away to have the new column round off the
numbers based on the Minute value. Up for 31-59 or down for 0-30?

TIA!!

 

[Bob Phillips]

First part =INT(A1*24)

Second part, see http://www.xldynamic.com/source/xld.Rounding.html#time

--

HTH

RP

I have one column full of time values. (example: 139:45:34, 165:45:13, etc).
I would like to create a column to the left of my time colum and display
just
the hour values (example: 139, 165, etc). How?

And, since I'm asking, is there away to have the new column round off the
numbers based on the Minute value. Up for 31-59 or down for 0-30?

TIA!!

 

[Frank Kabel]

Hi
=HOUR(A1)

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag I have one column full of time values. (example: 139:45:34, 165:45:13,
etc).
I would like to create a column to the left of my time colum and
display just
the hour values (example: 139, 165, etc). How?

And, since I'm asking, is there away to have the new column round off
the
numbers based on the Minute value. Up for 31-59 or down for 0-30?

TIA!!

 

[Bob Phillips]

Frank,

That returns the hour of day so to speak (quelle surprise!), so 139:45
returns 19 not 139

 

[Frank Kabel]

Hi Bob
guess you're right )
so for the OP: Use Bob's solution!