# How do I calculate an overdue date?

Options
✭✭✭

I have three potential approval date columns. How do I calculate the true overdue time in days?

My current formula doesn't account for approval dates 1, 2, or 3 being populated to signal completion...

=IF([Due Date]@row <= TODAY(), NETWORKDAYS([Due Date]@row, TODAY()) - 1, "")

• ✭✭✭✭✭✭
Options

Ok. It is the placement of the ROUND function. Instead of putting it around the entire IF, only put it around the NETWORKDAYS function.

• ✭✭✭✭✭✭
Options

Try also formatting the column to not show decimals and make sure it is the same across every single sheet. If you missed even one, the report will definitely show decimals.

• ✭✭✭✭✭✭
Options

Are you able to provide some screenshots for context?

• ✭✭✭
Options

I think I figured it out!

=IF(AND(ISBLANK([Date Round 1 Approved]@row), (ISBLANK([Round 2 Approval Status]@row)), ISBLANK([Final Approval Status]@row)), IF([Due Date]@row <= TODAY(), NETWORKDAYS([Due Date]@row, TODAY()) - 1, ""))

• ✭✭✭✭✭✭
Options

Glad you were able to get it working. 👍

• ✭✭✭
Options

Thank you, @Paul Newcome! I think this will capture the days overdue...I'd like to round it to not have decimals, but I'm getting zeroes where I should have blanks. Do you have a suggestion for how to approach this?

• ✭✭✭✭✭✭
Options

What is the updated formula for rounding?

• ✭✭✭
Options

The updated formula is:

=IFERROR(ROUND(IF(AND(ISBLANK([Date Round 1 Approved]@row), (ISBLANK([Round 2 Approval Status]@row)), ISBLANK([Final Approval Status]@row)), IF([Due Date]@row <= TODAY(), NETWORKDAYS([Due Date]@row, TODAY()) - 1, ""))), "")

To recap, I'd like to know the average number of days a request is overdue (in whole days).

• ✭✭✭✭✭✭
Options

Ok. It is the placement of the ROUND function. Instead of putting it around the entire IF, only put it around the NETWORKDAYS function.

• ✭✭✭
Options

Thank you, @Paul Newcome! I think this is working properly on the sheet side!

I'm still seeing decimals in the report and subsequent dashboards. There's no way to fix that, right?

• ✭✭✭✭✭✭