Count Number of Days Past Due

Taryn Buhler

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.

Paul Newcome

Give this a try...

=IF(AND(ISDATE([Due Date]@row), [Due Date]@row <= TODAY()), MAX(0, IF([Date Received]@row <> "", [Date Received]@row, TODAY()) - [Due Date]@row))

Adriane Price

Hello @Taryn Buhler - you can subtract due date from the date received to obtain your # of days late. I hope I understood your ask.

=[Due Date]@row - [Date Received]@row

Taryn Buhler

@Adriane Price Unfortunately, I need it to do the other functions as well and when combined with the need to count anything on time or early as a zero and adding in the If Error function, the Date Received minus Due Date formula doesn't work.

Paul Newcome

Try this:

=IF(ISDATE([Due Date]@row), MAX(0, IF([Date Received]@row <> "", [Date Received]@row, TODAY()) - [Due Date]@row))

Taryn Buhler

@Paul Newcome Almost there. It is giving the future due dates a 0, we'd prefer that to remain blank until the due date has been reached. But that did solve the initial problem.

Andrée Starå

Hi @Taryn Buhler

I hope you're well and safe!

Try something like this.

=IF([Due Date]@row > TODAY(), "", IF(ISDATE([Due Date]@row), MAX(0, IF([Date Received]@row <> "", [Date Received]@row, TODAY()) - [Due Date]@row)))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Give this a try...

=IF(AND(ISDATE([Due Date]@row), [Due Date]@row <= TODAY()), MAX(0, IF([Date Received]@row <> "", [Date Received]@row, TODAY()) - [Due Date]@row))

Taryn Buhler

@Paul Newcome I had to add an If Error at the start to account for the N/A, but yes! that works. Thank you so much!

Paul Newcome

Happy to help. 👍️

AGE_UT

@Paul Newcome Thank you sooo much!! I've been fussing with a similar issue for about 30 minutes (between testing things, Googling) and then finally came across this post and your solution. It was EXACTLY what I needed. Thanks for sharing your expertise!!!