Forum Discussion
turn off Excel date system 1900 and 1904?
Hello, I have issue with calculating AGE in my data set. More precisely, I have dates of incorporation, some of them have format dd/mm/yyyy, but some of them are only years e.g. 1967 which transform...
Hi Pavel
You could make all the date values as text (highlight then go to Home > Number Format Drop down >Text
Then if your data is in A1, A2 etc then in B1 type this
=IF(VALUE(A1)<2099,DATE(A1,1,1),TEXT(A1,"dd/mm/yyyy"))
This will then convert any year to the 1st of Jan each year, and every other date will be shown as the original date.
You can then calculate age ( I often use the YEARFRAC function to do that)
Cheers