Formula to Calculate items expiring in the next 10 days which are not closed yet

I am working on a formula on incident reporting to calculate items expiring in the next 10 days, where the status is not closed yet.

Can you advise if the following formula is correct?

=COUNTIFS({Expiry Date}, <=TODAY(10), {Status}, NOT(@cell = "Closed"))

I look at my sheet and there are only 2 items which are not closed and expiring in the next 10 days, but the calculation is showing 3. (1 item is expiring in next 10 days but already closed).

Would appreciate your feedback on this. Thanks.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Do you have any rows where the date is blank and the status is not "Closed"? Blank date cells are considered as less than today and blank text cells are not "Closed".

Vivien Chong

@Paul Newcome thanks for your quick response.

With reference to the following, as of today (9 July), it should be 3 items expired and not closed.

For items expiring in the next 10 days and not closed, it should be 0 now.

Screenshot 2020-07-09 at 10.49.19 AM.png

But the results I got is: (I am using a metric sheet to reference to the above sheet)

Screenshot 2020-07-09 at 10.51.24 AM.png

The formula I used for Expired without Closing: =COUNTIFS({Expiry Date}, <TODAY(), {Status}, NOT(@cell = "Closed"))

The formula I used for Expiring in 10 days: =COUNTIFS({Expiry Date}, <=TODAY(10), {Status}, NOT(@cell = "Closed"))

Do you have any idea if I have missed out on something?

Appreciate your input.

Thanks and have a blessed day!

Paul Newcome

Try this one for expiring in 10 days...

=COUNTIFS({Expiry Date}, AND(@cell >= TODAY(), @cell <= TODAY(10)), {Status}, NOT(@cell = "Closed"))

Vivien Chong

@Paul Newcome I think it works. I'll put in a few more dates to test the formula.

So, when I use the formula with just {Expiry Date}, <=TODAY(10), does it mean that it captures all dates before Today + 10? Trying understand these formula better. :D

Paul Newcome

That is correct. It will pull in everything that is also in the past. To avoid this, we enter the opposite side of the date range which in this case is TODAY().

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.