Changing colum data types independantly of display formatting

[jaypoc]

I have columns that are formatted times such as 100:24:48 but the actual data stored in the fields is a date 1/4/1900 4:24:48 AM.

I need to store the data as a TEXT field looking like 100:24:48

So far, attempts to convert the field have changed their displayed value, but not the actual value, or converted the text to a decimal or other value upon pasting into the text field.

How can I copy the Displayed values HH:MM:SS and paste into a text field and maintain the HH:MM:SS format.

I am using Excel 2007.

(I am aware that there are better ways to handle this data, but I am working with a third party tool which requires the content formatted a certain way. The format of the excel file has recently changed and I need to adjust it to accommodate the import function of the third party tool. Thanks in advance!)

 

[VoG]

Try

=TEXT(A1,"[h]:mm:ss")

 

[jaypoc]

*RESOLVED* Re: Changing colum data types independantly of display formatting

That worked.

I used the formula you provided, (which I tried earlier, and had some issues with. Perhaps I made a typo) which converted the field. I then pasted special values back into the same column and all looks good.

Thank you so much!