Formula to countdown contract expiration dates

Linda Armstrong

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?

Nick Korna

Hi @Linda Armstrong,

I think this formula will do what you're after:

=MAX(([Expiration Date]@row - TODAY()), ([Extension Date]@row - TODAY()))

Using some of your example data:

Any problems etc. let me know! 😊

Nick Korna

Hi @Linda Armstrong,

I think this formula will do what you're after:

=MAX(([Expiration Date]@row - TODAY()), ([Extension Date]@row - TODAY()))

Using some of your example data:

Any problems etc. let me know! 😊

Linda Armstrong

This is just wonderful. It worked like a charm. Thank you @Nick Korna!