Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've got some data from Taiwan in a .csv spreadsheet, but the dates are all in their patriotic calendar like this:

102/06/05
102/06/22
102/07/01
...

I need them in Gregorian format like this:

2013/06/05
2013/06/22
2013/07/01
...

The conversion is simple, just add 1911 to the year. Any ideas of a simple way to it in Excel?

share|improve this question

migrated from stackoverflow.com Jun 22 '13 at 20:15

This question came from our site for professional and enthusiast programmers.

    
Seems like this belongs on SuperUser, unless you want to do this in a program as opposed to using normal Excel. –  Joe Jun 22 '13 at 4:10

2 Answers 2

up vote 0 down vote accepted

If the existing Year, i.e. 102, is alway 3 digits then with data in A1 convert it to required date with this formula in B1

=REPLACE(A1,1,3,LEFT(A1,3)+1911)+0

format result in required date format, e.g. yyyy/mm/dd

share|improve this answer

To skip the problems Excel have when calculating dates before 01/01/1900 you can try to do something, if you think at your data as a string.

So if your data apears in your formula bar like this 102/06/05 you can simply try a CTRL+F, switch to replace, type to find 102/ string and replace with 2013/.

If this doesn't work you can edit the csv file with a text editor, and perform this replace, before you load the file in Excel.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.