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

Options
edited 12/09/19

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

Tags:

• ✭✭
edited 11/08/18
Options
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!
• Options

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

Thank you,

Sara

• ✭✭
edited 11/08/18
Options
You're welcome! Can you post a picture of the formula itself? I'll see if I can help out.
• Options

Thanks,

Sara

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

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

• ✭✭
edited 11/08/18
Options
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?
• Options

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

• ✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!