I want to count past due tasks using 2 date columns.

Hello all,

I'm trying to count overdue tasks by using 2 different date columns. I'm currently using this formula , =COUNTIFS([Hardware Actual Construction NTP]:[Hardware Actual Construction NTP], <[Hardware Planned Construction NTP]:[Hardware Planned Construction NTP])

However, whatever dates get put into the columns the count is always 0 and I'm not sure what I'm doing wrong. I'm hoping I can get it to count if the Actual start date is later than the projected start date.

Find more posts tagged with

Formulas

Accepted answers

Nick Korna

Hi @Cidniz

The easiest way to do this is to add a Helper column doing the check for you and then use this.

Helper column formula:

=IF([Hardware Planned Construction NTP]@row < [Hardware Actual Construction NTP]@row, 1, 0)

Then either:

=SUM([Helper column]:[Helper column]) - if you left the Helper column as a number

=COUNTIF([Helper column]:[Helper column], 1) - if you had the Helper column as a checkbox

Hope this helps, but any questions/problems then just ask! 🙂

All comments

Nick Korna

Hi @Cidniz

The easiest way to do this is to add a Helper column doing the check for you and then use this.

Helper column formula:

=IF([Hardware Planned Construction NTP]@row < [Hardware Actual Construction NTP]@row, 1, 0)

Then either:

=SUM([Helper column]:[Helper column]) - if you left the Helper column as a number

=COUNTIF([Helper column]:[Helper column], 1) - if you had the Helper column as a checkbox

Hope this helps, but any questions/problems then just ask! 🙂

Cidniz

That works perfectly, still new to Smartsheet and it never occurred to me to use a helper column and hide it. Thank you so much for the tip.

Nick Korna

No problem, glad to have helped! ☺️

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.