Filter Formula Based on Year

[Kevin Rush]

Hi,

I have a filter formula with works great, it check the value of a cell and then populates a table based on the info in a data sheet (Sheet "Report Data")

One issue I'm having is when the selection is "Year". I want it to look in the data sheet column H and return the row if the year is that selected in B12. It is import to note that the date isnt a year in the data sheet but a full date (dd/mm/yyyy) to address this is have tried the following

Please Login or Register  to view this content.

It hasn't worked.

Below I will breakdown the cells and references

Sheet "Report Data" is where the filter is getting the data from
Sheet "Map" is where selections will be made
Map B12 is where you select the year - Providing the year is what you are looking for (should out to AliGW for helping me with that)
Map A14 has a working Filter formula but doesn't include the year option I'm looking for
Map A15 has the formula I have tried.
Report data Column H is where the date is, as I said this is a full date but I want it to look for the year in that date.


I have attached the WB.

Thanks for your time. My appreciation on things like this is huge.

[Fluff13]

You have text in the column which is why you get the error, try

Please Login or Register  to view this content.

[Kevin Rush]

Thank You,

Unfortunately that didn't work

[Fluff13]

In what way?

[Kevin Rush]

Its says the syntax name isnt correct

[Bo_Ry]

Please try

=FILTER(Table1,IFERROR(XLOOKUP(A12,Table1[#Headers],Table1,YEAR(Table1[Target Return to Service Date - No Delays]))=B12,0))

[Kevin Rush]

Bo-Ry,

That seems to work, I have changed it to the heading I wanted (Next Out-of-Service Inspection Due Date). I will now try and add it to the end of my working formula.

Thank you

[Kevin Rush]

It worked!!

Thank you so very much.

What community!!

[Fluff13]

Its says the syntax name isnt correct

That's because you have a space at the start of the "Next Out-of-Service Inspection Due Date" which causes problems.