Convert year from YY to YYYY with year in 1900's

[joy1mat]

Hi, I'm working on a spreadsheet with 15K entries. All have the year as dd-mm-yy. The column is formatted as text. Some of the years are 1800's which I'm not worried about as these are all in YYYY. All the other dates are from the 1900's. I tried using the formula =TEXT(DATE(1900+MOD(YEAR(G2),1000),MONTH(G2),DAY(G2)),"dd-mmm-yyyy"). This works fine up to about the 1950's when the year goes to the 2800's. Can someone help with a formula that will keep all the years in the 1900's.

Joyce

 

[StephenCrump]

This works fine up to about the 1950's when the year goes to the 2800's.

It's not completely clear what you've got here. With 1900+MOD(YEAR(G2),1000), I'd have expected all 1900's dates to be converted to 2800's?

Will =TEXT(G2,"dd-mm-yyyy") suffice?

 

[mikerickson]

=SUBSITUTE(A1,"-","-19",2)

 

[joy1mat]

Hi Stephen

No =TEXT(G2,"dd-mm-yyyy") puts all the years into 2000 range and I need 1900's. I'm not sure why all the 1900's dates didn't convert to 2800's but those dates between 1900-1950 appear to work and those 1951 to 1999 go into the 2800 range.

I've attached a snip of a few lines of data where you can see the result of the formula with MOD included. The 1800 range isn't a problem as the #value error will be good when I have to sort the data into the date range. First I need to get the years into 1900's.

Any other thoughts on how I can do this?

Joyce

 

[StephenCrump]

Sorry, didn't read the question properly.

Mike's suggestion should work?

	A	B
1	01-Mar-01	01-Mar-1901
2	01-Mar-91	01-Mar-1991
3	01-Mar-1891	01-Mar-1891
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=IF(LEN(A1)>9,A1,SUBSTITUTE(A1,"-","-19",2))

 

[joy1mat]

Hello Mick and Stephen

The Substitute formula from Mick works. The 1800 years just has an extra 19 in front but with your updated formula with IF statement leaves these dates as they are. So this is a great solution. Many thanks to you both.

Joyce