calculating dates from column OR from current date

LizTo

Hello, I am trying to calculate the number of days that a report is overdue. This is the formula I am currently using, but there are some problems I need help to fix.

=IFERROR(NETDAYS([Final Report Due]@row, [Final Report Submitted]@row), 0) - 1

I added the -1 because it is calculating the NETDAYS rather than the difference between the days, so if a report is one day overdue, it was showing up as two, and if it was submitted on the due date, it showed up as one instead of zero.

The problem with the -1 is that any IFERROR results are now listed as -1 instead of 0.

For the rows where the final reports have not yet been submitted, I'd like to calculate it from the current date instead. Is there a way to do this?

Many thanks for any help or suggestions!

Paul Newcome

Try this:

=IF([Final Report Submitted]@row <> "N/A", IF([Final Report Submitted]@row <> "", [Final Report Submitted]@row, TODAY()) - [Final Report Due]@row, 0)

Paul Newcome

Try this:

=IF([Final Report Submitted]@row <> "N/A", IF([Final Report Submitted]@row <> "", [Final Report Submitted]@row, TODAY()) - [Final Report Due]@row, 0)

LizTo

@Paul Newcome Thank you, that worked perfectly!

Paul Newcome

Happy to help. 👍️