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

Find more posts tagged with

Formulas

Comments

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

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

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

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

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

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

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?