Count if Expiration Date is in the next 3, 14, 30 Days without counting Past Expiration Dates

Ava Blessie

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.

MichaelTCA

@Ava Blessie

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)

MichaelTCA

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.

Ava Blessie

@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.

MichaelTCA

@Ava Blessie

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".

Ava Blessie

@MichaelTCA

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

MichaelTCA

@Ava Blessie

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

@MichaelTCA

That worked perfectly!!! Thank you so much!!!

MichaelTCA

@Ava Blessie Of course! Anytime.