Pau Lo
asked on
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?
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?
ASKER
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
ASKER
Wont formatting it sending it off retain the century thus not solving the problem
ASKER
I just want to extract the relevant segments as text
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks john - I had the same result
@pma111 - You are very welcome and I was happy to help. I am glad this worked for you.
The easiest approach would be to correct the source of the data. Otherwise you would have to change the number to alter the century.