=Hour equation not working

[rcoon]

Hi Everyone,

I've formatted a column of times to a time format. Now I want to use the =hour equation, but I keep getting a "#Value!" message. I've tried every time format with no success. Hopefully someone can help me out, I'm driving myself crazy trying to solve this problem.

Thanks!

[MrShorty]

I put =ISTEXT(A2) into C2, and the result was TRUE, indicating that these are text strings that look like times and not true time serial values. You will need to use the TIMEVALUE() function to convert the text time to a serial number, inside of the HOUR() function. =HOUR(TIMEVALUE(A2))

[rcoon]

I put =ISTEXT(A2) into C2, and the result was TRUE, indicating that these are text strings that look like times and not true time serial values. You will need to use the TIMEVALUE() function to convert the text time to a serial number, inside of the HOUR() function. =HOUR(TIMEVALUE(A2))

Thank you! That worked perfectly!

[MrShorty]

I'm glad that worked. As I looked closer at your time values, I noticed that each value includes a leading and a trailing space, which, I expect, is part of why Excel had difficulty automatically detecting these as time values. You have not stated where these data are coming from, but, if the source puts leading/trailing spaces in the data, it is possible that other non-printing characters could also be included, possibly causing other difficulties with using these values. It may be worth the effort to explore the different possibilities and see if different text functions/strategies may be needed for other data sets to correctly convert these text strings to time values.

[rcoon]

I'm glad that worked. As I looked closer at your time values, I noticed that each value includes a leading and a trailing space, which, I expect, is part of why Excel had difficulty automatically detecting these as time values. You have not stated where these data are coming from, but, if the source puts leading/trailing spaces in the data, it is possible that other non-printing characters could also be included, possibly causing other difficulties with using these values. It may be worth the effort to explore the different possibilities and see if different text functions/strategies may be needed for other data sets to correctly convert these text strings to time values.

Thanks for bringing this to my attention. I will definitely look into it.