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?

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

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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