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.

Find more posts tagged with

Formulas

Accepted answers

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)

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.