Formatting Time values

[Sugar Ape]

I have a list of values from 00 to 59 that i want to convert to a time value (i.e., the list of values are minutes in a particular hour). To do this i am simply adding the hour to the value of the cell with the following formula:

Please Login or Register  to view this content.

I am them copying and pasting the values of the cell to get rid of the formula.
It works fine and the format of the cell is automaticaly recognised as hh:mm but it remains alligned to the left of the cell when you'd normally expect to see a time value alligned on the right by default. I can live the asthetic issue but it doesnt seem to recognise the contents of the sell if i try to compare to the value of another cell (e.g., using a VLOOKUP). The only way i can get round it is to simply double click on the cell to edit it. Without changing anything, if I then come out of the cell, the contents of the cell allign to the right and Excel seems to recongnise the contents.
Playing around with the format does not seem to work, only the double click.

I have around 5000 cells to go though so the thought of double clicking on each one doesnt appeal. Anyone know a way I can get these in to the right format en mass?

Thanks
Stu

[DonkeyOte]

Highlight the column -> Data -> Text to Columns -> Click Finish.

eg "12:00" is not a Time value per se - it is a text string that looks like a Time value but Time in XL is numeric (decimal - noon = 0.5 as 6am = 0.25 and 6pm = 0.75) - the above (Text to Columns) operation will coerce the strings to their numerical equiv.

[Sugar Ape]

Thanks for the quick response as always. Also realised after posting that finding and replacing the colon had the same affect.

[DonkeyOte]

Yes - essentially when you "enter" values into XL it tries to be "helpful" and interpret the values accordingly (eg 10/12 becomes a date etc).
Normally when XL tries to help it does the opposite however this TtC feature is in this type of instance perhaps the exception to the rule (?) given it essentially re-enters all of the constants and in doing so allows XL to re-interpret the "new" values accordingly - so it sees "12:00" as being text stored as number and converts to number (and thus time).