Formula for Gantt Progress

Garry Adams · 12/02/20

Hopefully I can explain what I'm trying to accomplish here...

When using the Project Template, SmartSheet shows the % complete overlaid on top of the duration bar in the Gantt Chart. Right now we're using colored icons to indicate whether a task is on target for completion (the progress bar is ahead of the "today" dashed line), behind target for completion (the progress bar is behind the "today" dashed line), or past due (the due date is in the past and % complete is not 100%). This quick color indicator is faster/easier than expanding the whole Gantt chart when doing a quick scan of the sheet, but it has its limitations too.

Right now this "on target" column is using the Red, Yellow, Green icon indicators, but it is a manual entry for a team member to look at the sheet and determine what status it should be. I read a recent post in the forum about using formulas to set an icon, but I'm not quite sure how I would go about automating this within my sheets.

I think I can figure out the past-due (red icon) by nesting an if statement for the % complete and comparing the "finish" date to today, but how do I determine if the % complete is ahead or behind the "today" line?

TIA!

Genevieve P. · 12/03/20

Hi @Garry Adams

To do this, you could create a percentage of how far through the project you are (by counting the days from the Start Date to Today's Date, then dividing this by the number of days from the Start to End).

Then you can compare this percent to the % Complete to see if you're behind or ahead of schedule!

Try something like this:

=IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF(NETDAYS([Start Date]@row, TODAY()) / NETDAYS([Start Date]@row, [End Date]@row) > [% Complete]@row, "Yellow", IF(NETDAYS([Start Date]@row, TODAY()) / NETDAYS([Start Date]@row, [End Date]@row) <= [% Complete]@row, "Green"))))

Here are some Help Articles I used to build this: NETDAYS function / TODAY function / @row function

Let me know if this works for you or if you want an explanation of what each of the statements is doing.

Cheers,

Genevieve

Garry Adams · 12/03/20

Thanks! I get the concept now. I was missing the NETDAYS function to count the number of days. I've noticed a couple of things in the logic that are not quite working as expected though.

First, I replaced NETDAYS([Start Date]@row, [End Date]@row) with Duration@row. Since the Duration field is already part of the Project Template, and the math is already done for me there, it made the formula a little more concise and easy to work with:

=IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF(NETDAYS([Start Date]@row, TODAY()) / Duration@row > [% Complete]@row, "Yellow", IF(NETDAYS([Start Date]@row, TODAY()) / Duration@row <= [% Complete]@row, "Green"))))

Second, SmartSheet includes the first and the last day when doing calculations. This makes sense for project management when calculating resource capacity, but it's a little screwy when doing math against dates. For example, if I had a start date of 12/1/2020 and and end date of 12/5/2020, SmartSheet considers that to be 5 days. Whereas by normal math, that's only 4 days (5-1=4). I noticed that the yellow icon didn't turn green until a day later in the Gantt than it should. This took some real experimentation. At first I just thought the formula was straight up wrong because sometimes it was yellow and sometimes green and I couldn't find the common thread. I had to work with some short and long date ranges in varying degrees of % Complete to tease out what SmartSheet was doing with the math. (And yes, I also tested this with the Duration@row vs the original formula and confirmed that they both acted the same.)

Once I realized this, I modified the formula to read as follows - notice the "-1" tagged to the end of each NETDAYS calculation before I divide by the Duration:

=IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF((NETDAYS([Start Date]@row, TODAY()) - 1) / Duration@row > [% Complete]@row, "Yellow", IF((NETDAYS([Start Date]@row, TODAY()) - 1) / Duration@row <= [% Complete]@row, "Green"))))

This math yields the correct percentage every time and the color now consistently follows the "Today" trendline and it flips to the right color when it should.

Thanks for your help and getting me pointed in the right direction!

Genevieve P. · 12/03/20