Need help with formula to calculate end date with % complete.
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
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! 
Thank you so much for the speedy response! I entered that formula and I'm receiving #unparseable. Any ideas?
Thank you,
Sara

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

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

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

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

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?

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

You’re welcome! Happy I could help, I hope you have a nice day.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!