Count if date is in the past

Hi All,

I am looking for a solution to a problem, I am trying to pull together a chart for a dashboard, displaying the number of overdue documents under review we have - the sheet is set up as below, and I have set up a 'metric sheet' however I am not sure this is the best solution. I have been using this =COUNTIFS({Master Document Tracker [DO NOT DELETE] Range 3}, <=TODAY()). But i have quickly realised, if there is an over due date in this column there will potentially be one in each column for this row. how do I count if any cells within a row have overdue dates but eliminate duplicate counts within the same row.

all suggestions welcome! thank you in advance!

Find more posts tagged with

Formulas

Reports

Dashboards

Accepted answers

Paul Newcome

Your syntax is a bit off. Try this:

=IF(COUNTIFS([Expected Draft Submit Date]@row:[Expected Doc Control Master complete]@row, @cell >= TODAY())> 0, "Overdue", "On Schedule")

All comments

Samuel Mueller

@Kirsteen Leckie You could use a helper column on this sheet that looks to see if there is any overdue dates, then count the helper column in your metric sheet. Formula could be something like this but will need to be modified:

=if(countifs([expecteddraftdate]@row:[expected submission]@row) >=today(), "Overdue", "On Schedule")

Then on your metric sheet count the number of "Overdue".

Does that concept make sense?

Kirsteen Leckie

@Samuel Mueller i like this idea - however the suggested has come back as #UNPARSEABLE.

i changed it to include the columns not shown in the picture - =IF(COUNTIFS([Expected Draft Submit Date]@row:[Expected Doc Control Master complete]@row >= TODAY(), "Overdue", "On Schedule")) but now coming back as #INCORRECT ARGUMENT SET

any ideas what I have missed?

Paul Newcome

Your syntax is a bit off. Try this:

=IF(COUNTIFS([Expected Draft Submit Date]@row:[Expected Doc Control Master complete]@row, @cell >= TODAY())> 0, "Overdue", "On Schedule")

Kirsteen Leckie

@Paul Newcome and @Samuel Mueller thank you both! exactly what I was looking for!!

Kirsteen Leckie

Samuel Mueller

@Paul Newcome Thanks Paul for the fix

Paul Newcome

Happy to help. 👍️

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.