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!)
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,116
Messages
5,835,476
Members
430,358
Latest member
zzc1128

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top