How do I calculate an overdue date?

mónicas

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, "")

Paul Newcome

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

Paul Newcome

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.

Paul Newcome

Are you able to provide some screenshots for context?

mónicas

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, ""))

Paul Newcome

Glad you were able to get it working. 👍

mónicas

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?

Paul Newcome

What is the updated formula for rounding?

mónicas

@Paul Newcome

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).

Paul Newcome

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

mónicas

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?

Paul Newcome

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.