Formula for delay between target and actual end date
Can anyone please breakdown and explain this schedule delta days formula that I have got from one of Smartsheets ready built template? I think in essence, it is trying to calculate the delay between the target end date and actual end date but because there are so many functions used in the formulate, it makes it a bit confusing.
Best Answer

Posting the formula into notepad and parsing it out a bit really helps when trying to understand longer formulas, especially when they repeat like this one (longer formulas usually follow a pattern)
=iferror(
if([end date]@row = [target end date]@row,0,
if([end date]@row > [target end date]@row, networkdays([target end date]@row, [end date]@row)1,
if([end date]@row < [target end date]@row, networkdays([target end date]@row,[end date]@row) + 1,"")))
,"")
if there is an error skip to the end
if the end date is equal to the target date, post a zero
if the end date is greater than the target date, take the number of days between them and subtract one
if the end date is less than the target date, take the number of days between them and add one
if there is an error, post a blank instead of the error code
Answers

Posting the formula into notepad and parsing it out a bit really helps when trying to understand longer formulas, especially when they repeat like this one (longer formulas usually follow a pattern)
=iferror(
if([end date]@row = [target end date]@row,0,
if([end date]@row > [target end date]@row, networkdays([target end date]@row, [end date]@row)1,
if([end date]@row < [target end date]@row, networkdays([target end date]@row,[end date]@row) + 1,"")))
,"")
if there is an error skip to the end
if the end date is equal to the target date, post a zero
if the end date is greater than the target date, take the number of days between them and subtract one
if the end date is less than the target date, take the number of days between them and add one
if there is an error, post a blank instead of the error code

Thank you so much!
Help Article Resources
Categories
Check out the Formula Handbook template!