Count Number of Days Past Due


Ultimate goal: Create a formula that will count the number of days an item is past due, while giving credit (by counting it as zero) for items completed early or on time all while ignoring where a due date may have an "N/A" or other issue in the field. This formula will then be used to create an average day past due calculation.

Problem: the current formula does not count the number of days the item is late. I'm guessing I need a TODAY or similar function in that last section of the IF statement, but I'm not getting one to work.

Columns: Due Date, Date Received, # of Days Late

Current Formula: =IFERROR(IF(([Date Received]@row - [Due Date]@row) < 0, 0, ([Date Received]@row - [Due Date]@row)), "")

The first two rows are "0" as expected, the third row should be "9" since today is the 24th, the fourth through sixth row the blank is correct since nothing is due.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!