9

Hello,

I need assistance with creating a formula to look at the end date and % complete columns. If the end date is nearing and the % complete is less than 100% the corresponding RYG should appear. The durations of each task varies from 1 day to 100 days depending on the project phase. What formula do you recommend for the formula to look at the end dates and % complete columns? Because the duration time varies - is there a universal formula I could use to look at the end date and % complete columns?

 

The specifications are:

Red: if the % complete is less than 70% and the end date is nearing 10 days out (today plus 9 days)

Yellow: If the % complete is less than 60% complete and the end date is nearing 15 days out (today plus 14 days)

Green: If the % complete is greater than 70% and the end date is greater than 15 days out

 

I look forward to your assistance. Please let me know if you have any questions.

 

Thank you!

Sara

 

 

Functionality

Comments

Hi Sara,
You could use a nested IF statement, comparing today's date to your due date and have it output the desired color. I made an example formula, but it doesn't account for projects that are nearing due date and almost done, or ones that are nowhere near complete and a long ways out. It fills anything that doesn't match your criteria with "Unknown".

=IF(AND([Due Date]1 > TODAY() + 15, [Percent Complete]1 > 0.7), "Green", IF(AND([Due Date]1 < TODAY() + 9, [Percent Complete]1 < 0.7), "Red", IF(AND([Due Date]1 < TODAY() + 14, [Percent Complete]1 < 0.6), "Yellow", "Unknown")))

Hope that helps!

I think we're getting close. Any ideas why I'm returning unknowns? I was hoping to make sure the RYG appears on the status column. 

Basically what I'm trying to track is the % completes and the estimated end date. I know it's a little tricky because of the different durations. 

I appreciate the speedy response and help. Thank you!!

In reply to by saras@focussch…

Those ones don't fall into the criteria since they're already complete, I wasn't sure what you wanted ones like that to appear as so I didn't account for it in the formula. I set it so anything that didn't meet the criteria appeared as "Unknown", would you like them to be green circles or say complete?