Changing format of time. Driving me nutz!

Hi there

I have a column of time cells formatted with a comma, ie 10,37, when I change the format of the cells to time format like this 13:40 it changes the numerical value of the cell!!!!! So 10,37 becomes 8:52 !!!!

I need to reformat the cell in a correct time format as I am combining a time and a date cell for look-up purposes. I tried using find and replace but it didn't help.

Thanks for any help.

Assuming in cell A1 you have 10,37. In B1 enter the following =VALUE(LEFT(A1,2)&":"&RIGHT(A1,2))

Thank you very much. : )

I am doing this for about 1500 rows, a few of which only have 3 digits of even 2 digits. ie 9,43 or 10 . is there any way to get around this or do I have to go thou and do those manually?

It a great solution for all the cells with 4 digits but with the 3 digit cells it can process and the 2 digit cells it will process incorrectly ie. if I have 10 (for 10am) it will give the result 10:10. Is there any way around this?