Formula to countdown contract expiration dates

I have a sheet that lists all active contracts for my department. The sheet houses the contract details such as Contract Name, Contract ID #, Vendor, Start Date, End Date and Extension Date, etc.… I am currently using =[Expiration Date]@row - today()  as the  formula in an adjacent cell to countdown the days until the contract End Date. This will allow me to pull a report into a dashboard to show contracts about to expire in 60, 120, 180 days …. However, my dilemma is I also need to consider any extension dates but when I add MAX into the formula =MAX([Expiration Date]2:[Extension Date]2@row - today()), I get an “invalid” prompt. In the example below the countdown for those contracts with extensions should say 340 as the countdown days not -25. Can someone assist with the correct formula?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!