Filter function returning #VALUE or #NUM error with dates

[dropkickweasel]

I am working on a workbook to manage stock.

I have a master table with a list of products on it.
One of the columns on the list is an expiry date, called "Exp".
Another column is a location where products can be stored, called "Location".

Each location has its own sheet.
FILTER draws through only the products from the master sheet that are stored in that location.
SORT automatically lists them by expiry date.
The formula looks like this:
=SORT(FILTER(Master,Master[Location]="Fridge","No Records"),7,1)

Conditional formatting is applied to the expiry dates so I can tell when a product is out of date / going out of date soon.
The conditional formatting formulae follow this pattern:
=EOMONTH(G1,-1)<TODAY()
This highlights products which will be out of date by the end of the month, given that the expiry date is listed in column G.

This all works fine.

What I want to do is create an additional sheet of "Products Expiring Soon".
This sheet would have all products that are already out of date, or will be out of date by the end of the third month from now, in all locations.
Note: Products that are out of date are typically "Destroyed". This is listed as a location and has its own sheet like the other locations.

If I use a formula like:
=FILTER(Master,(Master[Exp]<TODAY())*(Master[Location]<>"Destroyed"),"No Results")
This works to give me a list of all products in all locations that haven't been destroyed and are out of date as of today.

However, I want to list all products that will be out of date by the end of the third month from today.

I've tried to modify my FILTER formula like this:
=FILTER(Master,(EOMONTH(Master[Exp],-3)<TODAY())*(Master[Location]<>"Destroyed"),"No Results")
But this results in a #VALUE error.

Is there any way to change this formula so it does what I want it to do?



Other things I've tried are:

Current improvisation:
I change "Today()" to "Today()+90", effectively moving today three months into the future.
But, I would like the worksheet to be as robust as possible as I will not be the end user, so having it specifically only list products that will be out of date by the end of the third month from today would be ideal.

Attempted improvisation:
I created an additional column on the Master table called "Expiring".
The formula in this column is:
=EOMONTH([@Exp],-3)
This takes the expiry date from the Exp column and pushes it to the end of the month three months prior. So a date of "01/03/2022" becomes "31/12/2021" for example.

Problem:
Changing the formula on my "Products Expiring Soon" sheet to:
=FILTER(Master,(Master[Expiring]<TODAY())*(Master[Location]<>"Destroyed"),"No Results")
Results in a #NUM error that I just don't understand.
The source for both the Exp column and the Expiring column are dates. The only difference is that the dates in the Exp column have been inputted directly and the dates in the Expiring column are the result of an EOMONTH formula.

Does anybody have any advice on how I might go about solving this?

The Master sheet is where new stock is added, so I'd like to keep that in chronological order.
Products marked as "Destroyed" on the master sheet have their row hidden so that they do not clog up the Master, but are still visible on the Destroyed sheet.

 

[Fluff]

Check that the Expiring column does not have a #NUM error any where in it.

 

[dropkickweasel]

Aha!

It does!

There are a small number of products with a blank expiry date which is causing the error.

Thank you!

Do you know if there is any way to do what I want without needing the additional "Expiring" column? Some way of adding EOMONTH,-3 into the original FILTER function with the Exp column?

No worries if not, I can just hide the Expiring column once I know it's all working fine.

Many thanks :]

 

[Fluff]

You cannot use eomonth on an array, so will will either need to use a helper column or use something like "Today()+90"

 

[dropkickweasel]

Ah, fair enough.

It's good to know that I was along the right lines with my improvisation and it was only my lack of knowledge of array formulae that was letting me down.

Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[FormR]

Instead of the EOMONTH() function you could try something like this in its place.

DATE(YEAR(Mater[Exp]),MONTH(Master[Exp])-2,0)

 

[dropkickweasel]

Instead of the EOMONTH() function you could try something like this in its place.

DATE(YEAR(Mater[Exp]),MONTH(Master[Exp])-2,0)

Hi FormR,

If you have the time, could you explain to me what that formula is doing, please?

I added it in to my existing formula like this:

=FILTER(Master,DATE(YEAR(Master[Exp]),MONTH(Master[Exp])-2,0)*(Master[Location]<>"Destroyed"),"No result")

Initially I got a #NUM error, so went back to the Master sheet and filled in the few blanks with dates.

Now the formula pulls records through onto the Products Expiring Soon sheet, but it seems to pull everything through except products marked as "Destroyed", regardless of their expiry date.

Did I put the formula you gave me in the wrong place?

 

[Fluff]

Try it like

=FILTER(Master,(IFERROR(DATE(YEAR(Master[EXP]),MONTH(Master[EXP])-2,0),0)<TODAY())*(Master[EXP]<>"")*(Master[Location]<>"Destroyed"),"No result")

 

[dropkickweasel]

You folks are incredible!

That seems to do the trick perfectly without the need for an additional column.

Am I right in thinking that what this formula does is look at the date given in the Exp column, 'extract' the month from that date and take 2 away from it (effectively turning March into January) for example, then assess that date against Today() to determine if it hits the filter criteria or not?

I'm guessing that the MONTH function knows that 1 (January) -2 months = 11 (November) and not some mystery -1 month. Honestly, it might as well be magic at this point.

Thank you both for your help!