1

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?

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

2 Answers 2

Reset to default
0

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

Improve this answer
0

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.

Improve this answer

You must log in to answer this question.

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