date format issue

I have received some data from an external source. The current format of the data is shown as dd-mmm-yy, however, when you highlight a cell , in the formula bar - it shows dd/mm/yyyy (i.e. includes the century!).

The problem is the centuries are wrong for some cells of data. I want a separate column to copy the data - and only store dd/mm/yy - whereby yy represents only the last 2 year, i.e. for 2014 it should only store 14. I only want this data and I presume it would be better as text as opposed general/dates which may actually store the century information.

Any pointers of a formula?
LVL 3
pma111Asked:
Who is Participating?
 
JohnBusiness Consultant (Owner)Commented:
I exported a number of test dates into csv and the text looked like the format. However I don't know how CSV might work in this case.

So try a test:  Select a range or a sheet and format the date as you wish. Then export to CSV, close Excel and open just the CSV. My test says it might work.
0
 
JohnBusiness Consultant (Owner)Commented:
If the century is wrong, then the data is wrong. Formatting won't help this (although you can easily format d-mmm-yy in Excel.

The easiest approach would be to correct the source of the data. Otherwise you would have to change the number to alter the century.
0
 
pma111Author Commented:
I think its how excel handles dates before 2029 as opposed the source system but i need to export a list to csv in ddmmyy format
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pma111Author Commented:
Wont formatting it sending it off retain the century thus not solving the problem
0
 
pma111Author Commented:
I just want to extract the relevant segments as text
0
 
pma111Author Commented:
thanks john - I had the same result
0
 
JohnBusiness Consultant (Owner)Commented:
@pma111  - You are very welcome and I was happy to help. I am glad this worked for you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.