Variance formula between Baseline End Date vs Today vs Actual End Date

Ludivine

Hi Community,

How to get only 1 formula for combining :

If Today >= Baseline End Day = positive days behind schedule, if not "0"

If Task "Completed", showing the variance between Actual and Baseline End date

Thank you

Mark.poole

@Ludivine

Could we get a screenshot of what it is your working with. Be sure to clean it of any sensitive information.

Ludivine

How to get only 1 formula in column 'Behind schedule/Potential impact on delivery' showing the variance between Baseline End date vs Actual end date only if Actual date = >Today , if not showing "0'

And in the meantime how to STOP the countdown as soon as the status of the task is triggered as "Completed', but still showing the days behind schedule, if any.

Thank you

Ludivine

Hi @Mark.poole

Is there any formula that can fit our need in this case?

Thank you

Mark.poole

@Paul Newcome

Any Input on this one?

I haven't gotten this deep into Smartsheet yet so i am not sure how to answer.

Paul Newcome

You would use an IF statement as the base and write it out to follow the original post logic in reverse order.

If the status is completed, variance between end dates, otherwise the maximum of either zero or variance between today and baseline end.

=IF(Status@row = "Completed", [Actual End Date]@row - [Baseline End Date]@row, MAX(0, TODAY() - [Baseline End Date]@row)

Ludivine

Hi Paul,

Fantastic ! It works !

Thank you

Paul Newcome

Happy to help. 👍️