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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

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,114,523
Messages
5,548,547
Members
410,848
Latest member
anuradhagrewal
Top