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

Cidniz

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.

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! 🙂

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! ☺️