none
Customize Date field (Project 2003) with a function - what is not correct RRS feed

  • Question

  • Hi,

    i wanted to create a field that calculates a date (for QA weekly release) according to pre-defined cretiria:

    - the weekly release is always on wednsday

    - the date of a feature release to QA will be as followed:

        - weekly workdays are sunday until thursday

        - if finish date is before tuesday it will be released on the upcoming wednsday

          (for example:  finish date day is monday the 13 of Aug, release will be wednsday the 15 of Aug)

        - if finish date is tuesday and further then the release will be on the next week

          (for example:  tuesday the 14 of Aug will be relesed on 22 of Aug, also if the task is finished on the 15 or 16...)

    I created the following formula using "Day" function and "dateadd" function.

    But the result is always "NA" and not a date...

    Can someone explain what i did wrong and how can i fix the formula?

    IIf(Day([Finish])=1,dateadd("d",3,[Finish]),IIf(Day([Finish])=2,dateadd("d",2,[Finish]),IIf(Day([Finish])=3,dateadd("d",8,[Finish]),IIf(Day([Finish])=4,dateadd("d",8,[Finish]),IIf(Day([Finish])=5,dateadd("d",5,[Finish]),"NA")))))


    Ofir Marco , MCTS P.Z. Projects

    Sunday, August 12, 2012 8:54 AM

Answers

  • Hello Ofir,

    I believe the following formula will calculate all the Wednesdays from the Finish date, based on the given logic, provided that "Standard" is a base calendar with the working days defined from Sunday to Thursday.

    IIf(

    Weekday([Finish])<3,

    ProjDateAdd([Finish],CStr(4-Weekday([Finish])),"Standard"),

    ProjDateAdd([Finish],CStr(9-Weekday([Finish])),"Standard")

    )

    Regards.
    Sunday, August 12, 2012 12:50 PM
  • Thanks, it works!

    Ofir Marco , MCTS P.Z. Projects

    Monday, August 13, 2012 5:04 AM
  • IIf(

    Weekday([Finish])<3,

    Dateadd("d",4-Weekday([Finish]),[Finish]),

    Dateadd("d",11-Weekday([Finish]),[Finish])

    )

    I believe the above formula will do it.

    Regards.


    Wednesday, August 15, 2012 12:00 PM

All replies

  • Hello Ofir,

    I believe the following formula will calculate all the Wednesdays from the Finish date, based on the given logic, provided that "Standard" is a base calendar with the working days defined from Sunday to Thursday.

    IIf(

    Weekday([Finish])<3,

    ProjDateAdd([Finish],CStr(4-Weekday([Finish])),"Standard"),

    ProjDateAdd([Finish],CStr(9-Weekday([Finish])),"Standard")

    )

    Regards.
    Sunday, August 12, 2012 12:50 PM
  • Thanks, it works!

    Ofir Marco , MCTS P.Z. Projects

    Monday, August 13, 2012 5:04 AM
  • You're welcome. --Regards.
    Monday, August 13, 2012 9:32 AM
  • I guess it will be more difficult if i want to change the same formula so that the releases dates will be once in 2 weeks or once a month, right?  because if i change the "9" to "14"  the results can be every week and not 2 weeks that are permanent.

    Ofir Marco , MCTS P.Z. Projects

    Monday, August 13, 2012 11:16 AM
  • Hello Ofir,

    Defining a recursive task for this purpose seems to be a practical solution. In this case, the release dates will be determined based on the pattern defined, instead of the Finish dates. It will be relatively difficult to define a pattern in a formula.

    Regards.

    Monday, August 13, 2012 12:29 PM
  • There is one problem i found out with your formula.

    When there are some vacations on the calendar that was added (exceptions like holidays Etc...) in the working days the dates "jumps" into the next working day and sometimes changes to another day of the week (not wednsday...)


    Ofir Marco , MCTS P.Z. Projects

    Wednesday, August 15, 2012 7:26 AM
  • That's correct. This is the reason why we use the ProjDateAdd function. I guess the question is now whether we can schedule the release work on non-working days. If so, we can use the DateAdd function in the formula in order to schedule also in non-working days since the DateAdd function does not know about the project calendar.
    Wednesday, August 15, 2012 10:33 AM
  • Yeah i know that projdateadd uses calendar but i didn't want the release day to move on vacation permanently, i can have a holiday on monday and tuesday while wednsday is a working day but with projdate add the release will be 2 days later than dat release day (lets say sunday) and on the next weeks it will stay sunday and wont go back to wednsday....

    If i'll use the same formule but with dateadd instead of projadd it should work??

    because i tried it and it doesn't return values.


    Ofir Marco , MCTS P.Z. Projects

    Wednesday, August 15, 2012 11:33 AM
  • IIf(

    Weekday([Finish])<3,

    Dateadd("d",4-Weekday([Finish]),[Finish]),

    Dateadd("d",11-Weekday([Finish]),[Finish])

    )

    I believe the above formula will do it.

    Regards.


    Wednesday, August 15, 2012 12:00 PM
  • That did it alright!!

    Thanks alot!


    Ofir Marco , MCTS P.Z. Projects

    Wednesday, August 15, 2012 12:43 PM
  • You're welcome and thanks for the feedback. --Regards.
    Wednesday, August 15, 2012 3:08 PM