Excel 2010 - Extracting hour value from HH:MM:SS time - formatting issue

**asimbig** · 06-04-2013, 06:21 AM

Hello!

I have a long string of data of times (HH:MM:SS format) from which I need to extract the hour value (ie. 21:09:11 needs just 21)

Now the issue is when I click into the cells it gives me the value in a different format (H:MM:SS AM/PM) so when I try to use text to columns or use the formula to remove the 6 characters to the right of the hour value it gives me a different value to the one I need.

For example Text to columns gives me the hour value but for the am/pm figure meaning when I want "21" as the hour value it actually just gives me "9"

Test 1.xlsx

I have attached an example, have tried changing the format but it stays the same.

Any help would be most appreciated.

**Fotis1991** · 06-04-2013, 06:25 AM

Perhaps in B2 and copy down.

=HOUR(A2)

Format as general.

**asimbig** · 06-04-2013, 06:29 AM

Thank you so much! I was expecting such a complicated solution to this but I had no idea it would be so easy. Thank you so much!

**Fotis1991** · 06-04-2013, 06:32 AM

.......................

**arlu1201** · 06-04-2013, 06:47 AM

asimbig,

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.