How to calculate the number of days past due
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.
Best Answer
-
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 :-)
And regarding formulas
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
Answers
-
Hi Christine, the perfect use case for the networkdays formula, as it calculates the net working days between two dates.
Hope this helps
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
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.
-
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
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
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)
-
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?
-
@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.
-
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
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
@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
-
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())
-
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 :-)
And regarding formulas
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Thank you, sincerely appreciated!
-
Glad I could help!
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
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,
-
@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())))
Help Article Resources
Categories
Check out the Formula Handbook template!