Q:

Convert time fields to strings in Excel

I have an excel sheet full of times.
They are formatted so that they look like: 1:00:15
However if I change the format on the cells to text, they change to the underlying numeric representation of the time: 0.041840278
How can I convert the cells to be text cells but still have the time in them ?

A:

This kind of this is always a pain in Excel, you have to convert the values using a function because once Excel converts the cells to Time they are stored internally as numbers. Here is the best way I know how to do it:
I'll assume that your times are in column A starting at row 1.  In cell B1 enter this formula: =TEXT(A1,"hh:mm:ss AM/PM") , drag the formula down column B to the end of your data in column A.  Select the values from column B, copy, go to column C and select "Paste Special", then select "Values".  Select the cells you just copied into column C and format the cells as "Text".

A:

copy the column
paste it into notepad
copy it again
paste special as Text

A:

If you want to show those number values as a time then change the format of the cell to Time.
And if you want to transform it to a text in another cell:
=TEXT(A1,"hh:mm:ss")

