ms-project

Setting up a rolling 2 week filter in MS Project, missing the "Weekday" function

I want to set up a rolling 2 week look ahead filter in MS Project. I know how to add 14 days to today using now() + 14 however it would be better if it use the date of the Friday in 2 weeks time. Hence if I was to run it today (10th June) it would show tasks which started before Friday 28th June. I would also show the same tasks if I was to run it on the 11th, 12th ... 14th. This has two advantages - 1) it fits with how the business people think and 2) it allows me to run the report at any time.

I know I can set a filter which asks for a date but it would be nice if it did some of the thinking for the user.

I would prefer to avoid VBA if at all possible.

Thanks

Miles

Solution

  • Step 1

    The key to getting the 'next Friday date' regardless of what the current date is, is to use the DatePart function which returns a value of 1-7 using "w" for the interval argument.

    This formula will return the Friday 2 weeks out regardless of the current date. Current date = June 10 --> returns June 28. Current date June 14 --> June 28, but current date June 15 --> July 5. When the date rolls to the next week is controlled by the third argument 'firstdayofweek'. In this case we use Saturday (7).

    DateAdd("d", 21 - DatePart("w", Date(), 7), Date())

    Step 2

    The second part is to set up a custom Flag field with this formula. Note that the formula is shifted one day so that the inequality reads "finish before Saturday 2 weeks out" since dates in Project always have a time component and to say "Finish <= Friday" would miss all tasks finishing on that Friday since Friday 5:00 PM > Friday 12:00 AM.

    IIf([Finish]<DateAdd("d",22-DatePart("w",Date(),7),Date()),"yes","no")

    set up of custom flag field

    And finally, filter on that Flag field.