Changing colum data types independantly of display formatting

jaypoc

New Member
Joined
Mar 24, 2011
Messages
2
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!)
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

jaypoc

New Member
Joined
Mar 24, 2011
Messages
2
*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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,456
Messages
5,528,871
Members
409,842
Latest member
mfernandezcean
Top