How to calculate the number of days past due

Christine Lazzaro

Hi, I'm new to smartsheets! I'm trying to determine the formula for the following:

The project plan has a due date and and actual date completed, if the due date has past I need to determine the number of days late based on today's date or if the actual date completed is past the due date.

Stefan

Hi Christine,

the screenshots were a good idea ;-)

I can now see, that the names of your columns are different from what you posted here. Again, EXACT titles are required in formulas.

I assume, that the column with the actual completion date has the exact title "Date Completed". If not, replace the title in my formula with the correct version. Now try this:

=IF(ISBLANK([Date Completed]@row), IF([Due Date]@row < $today$1, NETWORKDAYS([Due Date]@row, $today$1), ""), NETWORKDAYS([Due Date]@row, [Date Completed]@row) )

Hope this helps

Stefan

PS: I hope you don't mind if I recommend the excellent Smartsheet learning tracks :-)

https://help.smartsheet.com

And regarding formulas

https://help.smartsheet.com/topics/Formulas%20and%20Functions

Stefan

Hi Christine, the perfect use case for the networkdays formula, as it calculates the net working days between two dates.

https://help.smartsheet.com/function/networkdays

Hope this helps

Stefan

Christine Lazzaro

Thanks, however I'm unable to determine the number of days late based on today's date or if the actual date completed is past the due date.

Stefan

Hi Christine,

I assume the column "actual date completed" remains empty until work is 100% completed. I also assume you have column "delay in days". And last, I assume all date columns are of the type DATE.

So the logic is:

Check if "actual date completed" is empty. -> Then check if "due date" is older than TODAY. -> If yes calculate due date versus TODAY / If no, calculate due date versus "actual date completed".

First you need a cell in the sheet, where you can place the TODAY function. You can use an extra column (like my example below), or you can place it in one of the other date columns e.g. in the first row.

Start easy by placing the ISBLANK formula only in the column "delay in days".

=IF(ISBLANK([actual date completed]@row), "empty", "not empty")

Now replace "empty" with the formula to check if due date is in the past.

IF([due date]@row < $today$1, "dd passed", "dd ahead")

=IF(ISBLANK([actual date completed]@row), IF([due date]@row < $today$1, "dd passed", "dd ahead"), "not empty")

Now "dd ahead" means no need to count so you replace it with "".

=IF(ISBLANK([actual date completed]@row), IF([due date]@row < $today$1, "dd passed", ""), "not empty")

Now "dd passed" means you need to calculate the difference between "due date" and "today".

NETWORKDAYS([due date]@row, $today$1)

=IF(ISBLANK([actual date completed]@row), IF([due date]@row < $today$1, NETWORKDAYS([due date]@row, $today$1), ""), "not empty")

And last you need to replace "not empty" with the calculation of the difference between "due date" and "actual date completed".

NETWORKDAYS([due date]@row, [actual date completed]@row)

Info: if the task is completed before the due date you will receive a negative number of days.

=IF(ISBLANK([actual date completed]@row), IF([due date]@row < $today$1, NETWORKDAYS([due date]@row, $today$1), ""), NETWORKDAYS([due date]@row, [actual date completed]@row) )

Hope this helps to get you going.

Stefan

Paul Newcome

Another option that does not require the TODAY function to be separate would be...

=NETWORKDAYS([Due Date]@row, IF([Actual Date Completed]@row <> "", [Actual Date Completed]@row, TODAY()))

You can nest an IF statement into the second portion of the NETWORKDAYS function to use either the [Actual Date Completed]@row or today's date depending on whether or not the [Actual Date Completed] column is blank.

This will generate the number. You can then nest this in an IF statement to generate a different output if you just want to (for example) flag a row that is/was past due.

=IF(NETWORKDAYS([Due Date]@row, IF([Actual Date Completed]@row <> "", [Actual Date Completed]@row, TODAY())) < 0, 1)

Christine Lazzaro

Thank you so much!

I can only get the first portion to work ( =IF(ISBLANK([actual date completed]@row), "empty", "not empty") I receive =UNPARSABLE every time I try the rest of the logic?

Paul Newcome

@Christine Lazzaro I believe Stefan continued to build off of that replacing certain portions with other functions. If I am not mistaken the final formula in his post was the end result of his logic.

You could also try...

=NETWORKDAYS([Due Date]@row, IF([Actual Date Completed]@row <> "", [Actual Date Completed]@row, TODAY()))

The above will be a little more efficient on the back-end in the event that you have a larger sheet, and it does not require putting the TODAY function in a separate cell.

Stefan

https://community.smartsheet.com/discussion/comment/255774#Comment_255774

Excellent and efficient suggestion Paul !

@Christine Lazzaro did you try?

=IF(ISBLANK([actual date completed]@row), IF([due date]@row < $today$1, NETWORKDAYS([due date]@row, $today$1), ""), NETWORKDAYS([due date]@row, [actual date completed]@row) )

If you get this working we could go on from there to eliminate the separate TODAY cell.

Hope this helps

Stefan

Christine Lazzaro

@Paul Newcome , I sincerely appreciate your assistance, it is not calculating properly, If the date completed is the same date as the date due is provides a result for the number of days delayed, if there is no date it is sporadic :

@Stefan Thank you for helping!!!!! Yes I did try it but still get the #unparseable

Paul Newcome

If you are not concerned with excluding holidays and/or weekends, you could use something such as...

=[Due Date]@row - IF([Actual Date Completed]@row <> "" [Actual Date Completed]@row, TODAY())

Stefan

Hi Christine,

the screenshots were a good idea ;-)

I can now see, that the names of your columns are different from what you posted here. Again, EXACT titles are required in formulas.

I assume, that the column with the actual completion date has the exact title "Date Completed". If not, replace the title in my formula with the correct version. Now try this:

=IF(ISBLANK([Date Completed]@row), IF([Due Date]@row < $today$1, NETWORKDAYS([Due Date]@row, $today$1), ""), NETWORKDAYS([Due Date]@row, [Date Completed]@row) )

Hope this helps

Stefan

PS: I hope you don't mind if I recommend the excellent Smartsheet learning tracks :-)

https://help.smartsheet.com

And regarding formulas

https://help.smartsheet.com/topics/Formulas%20and%20Functions

Christine Lazzaro

Thank you, sincerely appreciated!

Stefan

Glad I could help!

Zach Henderson

https://community.smartsheet.com/discussion/comment/255748#Comment_255748

This worked wonderfully for me. What if I wanted to ignore the formula if either start date or due date were null? I have this partially working but I dont know how to add the null due date part:

=IF(NOT(ISBLANK([Start Date]@row)), NETWORKDAYS([Due Date]@row, IF([Date Completed]@row <> "", [Date Completed]@row, TODAY())))

Thank you,

Paul Newcome

@Zach Henderson Try something like this...

=IF(AND([Start Date]@row <> "", [Due Date]@row <> ""), NETWORKDAYS([Due Date]@row, IF([Date Completed]@row <> "", [Date Completed]@row, TODAY())))