Count Past Due Tasks

Chris - NBAA

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

 

 

 

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