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.

Tags:

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭
edited 09/12/23

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)

• ✭✭✭

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

• ✭✭✭✭✭✭

@Ava Blessie Of course! Anytime.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!