Formula for date duration

Question

Hi-

I was trying to create a formula in Microsoft Project 2010.  The formula that I am trying to create is basically saying, take today's date, look 20 days before and 60 days ahead, then flag it as yes.  For instance, if today's date is 11/6, 20 days less is about 10/10, 60 days more is 2/11 (based on my working calendar).  Any finish date that falls in between 10/10 and 2/11 should be flagged as yes.

So I created a column- "Date 10", within "Date 10" the formula of [Current Date] was inserted.  So it automatically populates the current date.

From there, I tried developing the following formula to try and encompass the duration of 10/10 to 2/11:

IIf ([Finish]>=DateAdd("d",-20,[Date10]) And [Finish]<=DateAdd("d",60,[Date10])),yes,no)

For some reason, it isn't flagging the correct dates.  Anyone have any suggestions???

Thanks in advance!

Answer 1

Sorry for falling in. Just a hint: Use ProjDateAdd( date; duration; calendar )  instead of DateAdd to get non working days respected.

Regards

Barbara

Answer 2

You had added your holidays to the Standard calendar, yes?

Answer 3

Hmmm.  It appeared to be working fine using both auto scheduled and manually scheduled tasks.  What service pack do you have applied to Project 2010?  I am running Project 2010 with SP-2.

Answer 4

Hi Julie,

I tried entering your formula into the timeline and is coming up with the same issue.

The finish date is within the constraints of -20 days of today or greater than +60 days of today, however it is showing "No". Also, there are some finish dates outside the range of (-20 days and +60 days) which will show "Yes".

I do have a lot of non-working time in my calendar, hence my 60 days is projected much further.

I'll try adding the spare date fields.

Thanks again.

Answer 5

I had to clean up some syntax errors, but other wise your formula appears to be working correctly.

I am using the formula below in a Flag field:

IIf([Finish]>=DateAdd("d",-20,[Date10]) And [Finish]<=DateAdd("d",60,[Date10]),Yes,No)

When you say it isn't flagging the correct dates, can you be more specific? Is it not flagging summary tasks?  Did you set the summary task to use the formula?

I'm guessing you must have quite a bit of non-working time your calendar as using the Standard calendar (Monday through Friday with no holidays) 60 days from today is January 5.

You might try adding two spare date fields and calculating the minus 20 and plus 60 just to see if that is what is throwing off your flagging.