CODEWITHSUNDEEP
excelexcel-formula
jack
jack

Reputation: 1

Filter end date and add the filtered year to another excel column

I have a dataset (attached) with the start date and end date as some of the columns.

I want to filter the end date in such as way that end date > 09/01/2023 will move to fiscal year 2024. And end date < 09/01/2023 will stay as 2023.

If there are blank spaces, it should just ignore them.

I have another column called Year and I want the values (the year 2023 or 2024) to be displayed on this.

Thanks for your help!

enter image description here

EXPECTED OUTPUT:

enter image description here

Upvotes: 0

Views: 87

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

Try the following formula:

enter image description here

• Formula used in cell C2

=LET(
     a,B2:B36,
     IF((a="")+(a="FT Seasonal"),"",(IF(a="NA",DATE(2023,8,31),a)>=DATE(2023,9,1))+2023))

Or, without Spill:

=IF(OR(B2={"","FT Seasonal"}),"",(IF(B2="NA",DATE(2023,8,31),B2)>=DATE(2023,9,1))+2023)

Upvotes: 0

Related Questions