Count Past Due Tasks

Hi there - I'm new to formatting / formulas in smartsheet (and excel for that matter) and haven't had much luck creating a formula that counts tasks that are past due. While able to use conditional formatting to visually identify these items I'm trying to roll this up into a project overview; the overview/dashboard - currently includes task count by owener, by status, etc.

Am I on the right track using a COUNTIF and TODAY type functions? Perhaps a formula that would assign a count or criteria to any dates that are greater than today and, from there use a COUNTIF to count up the number of those items? My attempts thus far have returend various errors.

I appreciate any assistnce the community can offer!

-C

Find more posts tagged with

Non-profit

Conditional Formatting

Project Management

Comments

John Hammond

You are on the right track with COUNTIF and TODAY functions. Try this...

Add a checkbox column to your sheet with a formula that will check the box if the corresponding due date is in the past.

=IF([End Date]1 < TODAY(), 1)

Then build a COUNTIF that counts the number of boxes checked.

=COUNTIF(CB:CB, 1)

CB is the name of the checkbox column. By not including row numbers, it will count the entire column and not just a range.

Derek Nicholas

I am trying to do something very similar. I would like to do it without adding additional columns. Can anyone tell me why this formula will not work or what can be done to correct it.

=COUNTIF([Due Date]:[Due Date], - TODAY(),>0)

I get #MISSING OR INVALID PARAMETERS

I have also tried this with same results.

=COUNTIF([Due Date]:[Due Date], <TODAY())

John Hammond

It will not work because you can only use < or > in a COUNTIF with numerical values - you cannot use dates.

From the help center article on formulas:

Example: =COUNTIF(Done:Done, 1)
Result: 2
Example: =COUNTIF(Cost:Cost, ">" + 200)
NOTE: This syntax works for numeric values only.
Result: 2

Val

Hi Chris, can you show us a sample of the formulas, My skills are less than yours and I am trying to do some similar to what you did. I will really appreciate that.

Thanks