Help with a Formula

I have a Target % Complete column in a project plan template that essentially looks to see if you are tracking to the % complete you should be based on today's date and the start and end date of any task (or tells you that you are missing dates).

I've included the formula below that returns the result of what your target % complete should be. The thing is, when a task is indicated @ 100%, the cell with this formula goes blank. I want it to say 100% but can't tell where to update the formula to make this happen.

Any thoughts?

=IFERROR(IF(Status@row <> "Completed", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) > 1, "Future", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) < 0, "Late", IF([Planned Start]@row = [Today (hidden)]$1, 1 / NETDAYS([Today (hidden)]$1, [Planned End]@row), 1 - (NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row)))))), "Missing Dates")

Find more posts tagged with

Formulas

Project Management

Comments

Genevieve P.

It looks like all you’re missing is the “Value if False” part of the very first IF statement. You have instructions for what to do if the Status is not Completed, but there’s no indication of what to do if the Status is completed.

For example (without the encompassing IFERROR statement for clarity):

IF(Status@row <> “Completed”, {rest of your formula}, “100%”)

Let me know if this makes sense / works!

Cheers,

Genevieve

LLaCosta

Thank you - Conceptually I understand what is missing based on how you outlined it. My question is where you have {rest of your formula}....am I just adding the "100%" in quotes to have it return that value? I am not sure how to create the "rest of your formula"

Genevieve P.

My apologies for not being more clear - the {rest of your formula} indicated the rest of the formula that you have already written/created. I'll copy/paste it below.

In reviewing this, it looks like your column might already be set up as a percent column, is that correct? If that is the case, instead of saying "100%" (which would return that value as text), you can just add: 1. The 1 will equal 100% in a percent column.

I've bolded the only addition to your formula above (the , 1). Let me know if this works!

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?