Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .