Formula for percentage complete and duration remaining

Jamila

I am trying to develop a formula to flag tasks that are less than 50% complete but more than 50% of the duration has passed. I'm not sure if my math is correct. But aside from that, can Smartsheet perform functions like the one I am proposing below? Currently, it returns an unparseable error. If I get this formula to work, I would incorporate the checked box in that column into another formula.

=IF(((([End Date]@row – TODAY())/[Duration]@row)< 0.5), 1, 0)

Schiff A.

Hi @Jamila!

This is definitely possible. Here's how I approach the problem:

• Our threshold is half the number of days into the task or the duration divided by 2
• The Duration column in Smartsheet is based on working days so we'll need to use NETWORKDAYS to find the correct number of days that have passed

In plain language, the criteria are:

1. The number of days that have passed since the beginning of the task is greater than half the number of days in the task (or past the halfway point)
2. The progress is less than 50%

My formula for that looks like this:

=IF(AND(NETWORKDAYS([Start Date]@row, TODAY()) > (Duration@row / 2), Progress@row < 0.5), 1, 0)

Let me know if you have any questions!

Schiff

Solution Implementation Manager

Smartsheet

Schiff A.

Hi @Jamila!

This is definitely possible. Here's how I approach the problem:

• Our threshold is half the number of days into the task or the duration divided by 2
• The Duration column in Smartsheet is based on working days so we'll need to use NETWORKDAYS to find the correct number of days that have passed

In plain language, the criteria are:

1. The number of days that have passed since the beginning of the task is greater than half the number of days in the task (or past the halfway point)
2. The progress is less than 50%

My formula for that looks like this:

=IF(AND(NETWORKDAYS([Start Date]@row, TODAY()) > (Duration@row / 2), Progress@row < 0.5), 1, 0)

Let me know if you have any questions!

Schiff

Solution Implementation Manager

Smartsheet

Jamila

This is great! My way was a lot more complicated. I had to change "Progress" to the column name in my sheet "Percentage Complete" so that it works. Now I know about NETWORKDAYS as well.

Thanks