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

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…