Nesting Formulas

Darcy Canaday

Thanks in advance for any help!

Doing a gantt with dependencies turned off and want my % complete column to calculate the percentage along with an IF statement so that start dates in the future return a 0% complete answer and finish dates in the past return a 100% complete answer.

I know how to do each of them individually but I don't know how to put them together.

The IF statement I'm using is =IF([Start Date]@row > TODAY(), "0", IF([End Date]@row < TODAY(), "100%"))

The regular percentage calculation I'm using is =(TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)

How do I nest them?

Nick Korna

Hi @Darcy Canaday,

Is this what you're after?

=IF([Start Date]@row > TODAY(), 0, IF([End Date]@row < TODAY(), 1, (TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)))

This will return a decimal if not 0/1, but you can change your column to display percentage and this will show as between 0 and 100%.

Example:

Hope this helps - if you've any questions then just ask! 🙂

Darcy Canaday

Yes @Nick Korna , thank you! I also found someone in our office who is an Excel wizard and came to the same solution about 20 min ago and it works perfectly! Thank you for taking the time!