IIF statement in MS Project

Question

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

Answer 1

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,

Answer 2

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

Answer 3

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

Answer 4

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,

Answer 5

Why not just use the [Remaining Duration] column

That gives you the number of working days to go.