Convert time fields to strings in Excel

Question

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 ?

Answer 1

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".