Extract Hour Only from HH:MM

[Bench]

Hi,

Trying to extract the HH from a cell containg HH:MM

for example if B1 has 10:19 in, i would like C1 to show 10, or even 10:00 would suffice.

i have tried =LEFT(B1,2) this returns 0.

=LEFT(B1,2) returns 0.4 strangely enough

I guess theres an issue because the cell is formatted as hh:mm

Any ideas? Is there an alternative to Left that i should be using?

Thanks

 

[Domski]

=HOUR(B1)

Dom

 

[Domski]

The reason you got this:

=LEFT(B1,2) returns 0.4 strangely enough

is because time stored as a serial value with a day being equal to 1 and any part day being a fraction of that. The serial time value for 10:19 is 0.429861111111111

Dom

 

[Bench]

=HOUR(B1)

Dom

Thanks, i tried this but again i got a strange result

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20 width=64 align=right>00:00</TD></TR></TBODY></TABLE>

any idea why that would be?

 

[Domski]

=HOUR(B1) will return the number of hours as an Integer so you would need to format the cell as a number.

To return is as a serial time value you could use: =B1-MOD(B1,1/24)

Dom

 

[Bench]

=HOUR(B1) will return the number of hours as an Integer so you would need to format the cell as a number.

To return is as a serial time value you could use: =B1-MOD(B1,1/24)

Dom

Thats great, all working now

Thanks