# calculating dates from column OR from current date

Options
✭✭✭✭✭

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!

• ✭✭✭✭✭✭
Options

Try this:

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

• ✭✭✭✭✭✭
Options

Try this:

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

• ✭✭✭✭✭
Options

@Paul Newcome Thank you, that worked perfectly!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!