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, 2023 in Others by Kithuzzz
• 38,000 points
423 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, 2023 by narikkadan
• 63,600 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
• 63,600 points
1,120 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
• 63,600 points
1,632 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
• 63,600 points
771 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
• 63,600 points
1,691 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, 2023 in Others by narikkadan
• 63,600 points
520 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
1,258 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,020 points
1,120 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,020 points
4,394 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
• 63,600 points
1,626 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