Deal with 1900 year dates

[Mafy]

Hi everyone,

Just followed the steps recommended by 'Mr. Excel' on how to deal with dates before 1900. It works perfectly well, except when the ended year date is 1900.

birth 20/07/1898
death 01/01/1900
birthmodified 20/07/2898 Formula =LEFT(C2,LEN(C2)-4)&RIGHT(C2,4)+1000
deathmodified #VALUE! Formula =LEFT(D2,LEN(D2)-4)&RIGHT(D2,4)+1000

Any ideas on how to sort this out are very welcome.

Thank you in advance, Mafy

 

[daregu]

I am just spitballing here, but maybe doublecheck to make sure that the 1900 date being stored in D2 is being stored as text and not as a date and see if that helps.

 

[Alex Blakenburg]

Excel is not recognising < 1900 as a date so you are working with a text value.
From 1900 Excel does recognise it as a date so you are working with a number/date.

What are you trying to do ?

 

[jasonb75]

See if this works,

=IFERROR(EDATE(D2,12000),LEFT(D2,LEN(D2)-4)&RIGHT(D2,4)+1000)

It is likely that this will show the date serial in the cell, in which case you would need to format the cell as dd/mm/yyyy