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

saras54661
edited 12/09/19 in Formulas and Functions

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:

Comments

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

    Screen Shot 2018-11-08 at 2.47.30 PM.png

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

    Screen Shot 2018-11-08 at 3.01.40 PM.png

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

    Screen Shot 2018-11-08 at 3.17.36 PM.png

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!