Share via

IIF statement in MS Project

Anonymous
2013-12-30T19:33:55+00:00

Hi everybody,

I'm trying to write a formula for column [days remaining] that returns the days remaining to complete each task, so a datediff(), however, if [% Complete] = 100% for that task, then show "0" and don't descend into negative numbers.

Columns used are:

[Finish] which is a date when each task should finish.

[% Complete] which is a percentile written out in text such as "45%".."100%"..etc

[remaining days] which is where this custom field/formula is going.

The formula (as I think I should write it but isn't working) goes as follows:

iif([% Complete]="100%","0",datediff("D",date(),Finish))

It's returning everything as the date diff.

Help and thanks,

-HK

Microsoft 365 and Office
Microsoft 365 and Office

A comprehensive suite of productivity tools and cloud services that enhance collaboration, communication, and efficiency. Combining classic Office apps with advanced Microsoft 365 features, it supports both personal and business needs

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

Anonymous
2013-12-30T20:22:15+00:00

Hi,

% Complete is a number so it should read 100

And what is "D"???

Also, Finish is the planned completion date, not "when it should finish", but that may be semantics.

Better use Projdatediff which takes the project calendar into account

greetings,

Was this answer helpful?

0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Anonymous
    2013-12-31T16:08:59+00:00

    The "d" in the DateDiff Function is the required string argument stating the time interval.  "D" for day, "h" for hour, etc.

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2013-12-31T14:03:22+00:00

    Hi,

    % Complete is a number so it should read 100

    And what is "D"???

    Also, Finish is the planned completion date, not "when it should finish", but that may be semantics.

    Better use Projdatediff which takes the project calendar into account

    greetings,

     

    From what I understand, "D" returns a single digit number as the result of the datediff. But your 100 was what I was missing. THANKS! (I'm more an Excel expert so these early attempts at my using project, the formulaic differences are slightly frustrating/confusing.

    thanks again,

    HK

    Was this answer helpful?

    0 comments No comments
  3. Anonymous
    2013-12-31T11:46:32+00:00

    Hi Rod,

    Here's the answer to the Why question.

    Remaining duration does not take today's date into account; IMHO the user is looking for the time left from planned finish till today.

    Greetings,

    Was this answer helpful?

    0 comments No comments
  4. Anonymous
    2013-12-31T00:06:09+00:00

    Why not just use the [Remaining Duration] column

    That gives you the number of working days to go.

    Was this answer helpful?

    0 comments No comments