Convert date from Taiwan to Gregorian in Excel

Question

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?

Answer 1

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

Answer 2

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.