Need help with formula to calculate end date with % complete.

saras54661

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

 

 

Sterling

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!

saras54661

Thank you so much for the speedy response! I entered that formula and I'm receiving #unparseable. Any ideas?

 

Thank you,

Sara

Sterling

You're welcome! Can you post a picture of the formula itself? I'll see if I can help out.

saras54661

Here you go and thank you. I took the 201 class with Lauren yesterday and forgot to ask her about this formula.

Thanks,

Sara

Sterling

No worries! you just need to change the column names to match yours, that should fix it.

saras54661

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!!

Sterling

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?

saras54661

Thank you so very much! I think I got it. Thank you again!

Sterling

You’re welcome! Happy I could help, I hope you have a nice day.