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 4
pma111Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
pma111Author Commented:
Wont formatting it sending it off retain the century thus not solving the problem
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pma111Author Commented:
I just want to extract the relevant segments as text
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.