Count if Expiration Date is in the next 3, 14, 30 Days without counting Past Expiration Dates
My Formula is "=COUNTIF({Expiration Date}, <TODAY(3))" switching out the number for the appropriate timeframe.
When I do a filter on the sheet it is referencing to there is only 1 expiring in the next 3 days however the formula keeps giving me an answer of 2.
I am unsure if it is counting the 2 that are past the expiration date or what.
Best Answer

The easiest way would be to create a helper column for the days remaining until the expiration date and reference that column in the function.
=COUNTIFS({Expiration Date}, <TODAY(3),{Days Remaining}, >0)
Answers

Hello @Ava Blessie
Have you tried less than or equal to <=TODAY(3)?
If you're only using <, the function won't include day 3, only everything less than that.

@MichaelTCA I did have that in my original Formula I must have not had it copied over; its still counting the ones that are past the expiration date.

Got it. You will need to use the COUNTIFS function, with a S at the end. Add another criteria that requires the difference between today and the expiration date to be greater than 0. I like to use the NETDAYS function.
If the difference is less than 0, it's technically "in the past".

Oh thank you!! So how would you rewrite that formula?

The easiest way would be to create a helper column for the days remaining until the expiration date and reference that column in the function.
=COUNTIFS({Expiration Date}, <TODAY(3),{Days Remaining}, >0)


@Ava Blessie Of course! Anytime.
Help Article Resources
Categories
Check out the Formula Handbook template!