Need a formula for tasks/milestones due "Next" week

• Question

• Hi, can anyone help me perfect a formula to list "next" weeks tasks eg This Friday is the status date and I want to know which tasks should complete or progress upto the following Friday.

My initial stab at this is shown below but it isn't right - any ideas anyone?

IIf([Start]<=([Status Date]+7),IIf([Stop]<([Status Date]+7) or IIf([Stop]="NA", IIf([% Complete]<100, IIf([Summary]=No, Yes)))))

Friday, March 1, 2013 3:33 PM

All replies

• Stev, you should always be cautious when trying to add or subtract days and dates in Project - the default unit for dates and durations is minutes and it can get pretty confusing. Use the functions when building your formulae, in this case DateAdd.  It may be possible to simplify it, but for your requirement I'd start with a formula like:

IIf((([Start]<=DateAdd("d",7,[Status Date])) And (projdatevalue("NA")=[Actual Start]))     Or      (([Finish]<=DateAdd("d",7,[Status Date])) And (projdatevalue("NA")=[Actual Finish]))     Or     ([% Complete]<>0 And [% Complete]<>100),Yes,No)

You can see that there are three Or functions - testing the start date, the finish date and whether the task is in progress (good for long-running tasks). I'm not sure why you would want to use the Stop date in your formula.

Graham

Friday, March 1, 2013 4:47 PM
• Hi thanks for this, I'll test this out and let you know how I get on.  I was using the STOP date to help me identify tasks that would due to progress but not finish upto the Status date + 7

thanks

Friday, March 1, 2013 6:12 PM
• hi Stev Scott42,

Pardon me for bumping in - but I don't believe the Stop field does that.  From help:

The Stop field shows the date that represents the end of the actual portion of a task.

So, if today is Friday and the last date I have actual recorded in the task is Wednesday, the Stop date would equal Wednesday.There is no implied "due to" in the field.  Your formula portion IIf([Stop]<([Status Date]+7) is probably always going to evaluation to "true" unless your Status Date is weeks ago or you have been recording actual work in the future.

I hope this helps.

Saturday, March 2, 2013 1:39 AM