Conditions in Excel skip cells with dates without a year

0 votes

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.

Jan 17 in Others by Kithuzzz
• 28,700 points
41 views

1 answer to this question.

0 votes

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.

answered Jan 17 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 53,160 points
148 views
0 votes
1 answer

How to merge two cells in excel with same field name

Insert 2 new columns, G & H. Enter ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 53,160 points
168 views
0 votes
1 answer

In Excel, how to find a average from selected cells

If one has the dynamic array formula ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 53,160 points
160 views
0 votes
1 answer

Number format in excel: Showing % value without multiplying with 100

You just need to select Custom from ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 53,160 points
86 views
0 votes
1 answer

Conditional Formating in Excel comparing dates between cells

To get a date from your strings, ...READ MORE

answered Jan 26 in Others by narikkadan
• 53,160 points
53 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
530 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
477 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,499 views
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 53,160 points
109 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP