Date format: recent 2-digit years registering as 1900s

[troglodytes]

Hello,

I'm using the following formula on B2
=ROUNDDOWN((TODAY()-DATE(E2;C2;D2))/365,25;1
to display the current ages of a list of people on column A.

E2 is the 2-digit year, and recently some people born in the 2000s have been added to the list, making my life difficult. If I type 2000 the cell is so small that it can't display it (##)
I guess I could easily solve this by making all data into 4 digits and resizing the E column, but I'd like to know if it's possible to type only "00" and the formula actually reads it as 2000 instead of 1900

Any help is appreciated.

PS: To clarify, I know about the Regional Setting in Windows Control Panel.
Excel actually converts dates correctly when it's complete, for example 5/15/19 to 05/15/2019
But if I custom format the cell to display just the year (yy or yyyy) and I type 19, Excel thinks it means 01/19/1900, and if I type 2019 it converts to 07/11/1905 for some reason.

 

[Rick Rothstein]

Assuming all of your dates are always two digits long, try changing your formula to this...

=ROUNDDOWN((TODAY()-DATE(1900+E2+100*(0+E2<30);C2;D2))/365,25;1)

where I have assumed 1930 to be the cutoff between 1900 dates and 2000 dates in your computer.

 

[troglodytes]

It worked perfectly, thank you very much