Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Count Past Due Tasks

Chris - NBAA
edited 12/09/19 in Archived 2015 Posts

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!






  • John Hammond
    edited 09/24/15

    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 have also tried this with same results.

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


  • John Hammond
    edited 03/01/16

    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.


This discussion has been closed.