Conditions in Excel skip cells with dates without a year

Question

I have the results of a survey with birthdays in various date formattings.

• 01.01.1990
• 02/03
• 04.05 etc.

Excel automatically inserts the current year into the cell whenever it encounters a day and a month without a year. Therefore, when we attempt to extract a year, we receive the current year instead of the error we anticipated, which is absurd.

How can we stop doing this?

The desirable result is:

1. One column with one formula
2. If the year is written, we extract it using =YEAR()
3. If it is absent, we just do anything else, for instanse put "".

The implications of the current year remain unchanged after a simple formatting adjustment. Additionally, the formula "YEAR" cannot be used to any of the cells if the entire column's format changes from date to text.

Only students who are comfortable using Excel and Google Sheets should attempt this task; Python is not an option.

Answer 1

Both Google Sheets and Excel save dates as numbers (day counts) starting on 1900/01/01. As a result, they either automatically assume the year for you or fail to detect the date at all. Date to number conversion: 1900/01/01 = 1, 2023/01/16 = 44942 (because it is 44942 days since 1900/01/01).

People born in this year can't, in my opinion, complete the survey, so just "filter" them out:

If date is in A1 use the formula:

=IF(OR(YEAR(A1)=2023,YEAR(A1)=1900),"",YEAR(A1))

I hope this helps you.