Discussions

Expand all | Collapse all

Formula date field third Wednesday

  • 1.  Formula date field third Wednesday

    Posted 22 days ago
    Looking for a formula date field calculating the third Wednesday after a date field "Assignment Date" for example;

    If the "Assignment Date" is today (18th of March) then this "Date field (Lets call it "End Date"" should show 8th of April.

    ------------------------------
    Ermias Bean
    ------------------------------


  • 2.  RE: Formula date field third Wednesday

    Posted 22 days ago
    Edited by Brandon Drake 22 days ago
    Hello Ermias, 

    I think the best way to achieve this would be to use the NextDayofWeek([Assignment Date], 3) to find the next Wednesday and then add 21 days.  I think this may work.

    if(DayofWeek([Assignment Date] !=3,
    NextDayofWeek([Assignment Date], 3) + Days(21),
    [Assignment Date] + Days(21))

    The only caveat is you did not really explain what happens if the assignment date is a Thursday.  Is it then the third Wednesday after the next Wednesday, or is that next Wednesday part of it, then that would make the formula more complicated, but still doable.

    ------------------------------
    Brandon Drake
    JHI Group
    Monroeville OH
    ------------------------------



  • 3.  RE: Formula date field third Wednesday

    Posted 22 days ago
    Thanks for the help. The Assignment date can be any given day of the week

    ------------------------------
    Ermias Bean
    ------------------------------



  • 4.  RE: Formula date field third Wednesday

    Posted 22 days ago
    Today just happens to be Wednesday.

    ------------------------------
    Ermias Bean
    ------------------------------



  • 5.  RE: Formula date field third Wednesday

    Posted 22 days ago
    Understood.  The question is, if the Assignment Date is on Thursday the 19th, would the End Date still be April 8th since it was assigned in the same week, or would it get pushed out to the 15th of April since it was assigned after the nearest Wednesday?

    ------------------------------
    Brandon Drake
    JHI Group
    Monroeville OH
    ------------------------------



  • 6.  RE: Formula date field third Wednesday

    Posted 22 days ago
    the end date will still be April 8th

    ------------------------------
    Ermias Bean
    ------------------------------



  • 7.  RE: Formula date field third Wednesday

    Posted 22 days ago
    Ermias,

    Since today is Wednesday the 18th, does that count as one Wednesday on the way to three or is today always zero on the way to three?

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 8.  RE: Formula date field third Wednesday

    Posted 22 days ago
    No it doesn't count. it is 3 Wednesdays after today - with April 8th being the third Wednesday.

    ------------------------------
    Ermias Bean
    ------------------------------



  • 9.  RE: Formula date field third Wednesday

    Posted 22 days ago
    Ermias,

    Try this as a Formula Date Field

    var Number DayNumber = DayOfWeek([Assignment Date]);
    var date TodaySunTue = NextDayOfWeek([Assignment Date], 3)+ Days(14);
    var date TodayWedPlus = [Assignment Date]-Days($DayNumber-3) + Days(21);

    If ( $DayNumber<3,  $TodaySunTue,  $TodayWedPlus )

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 10.  RE: Formula date field third Wednesday

    Posted 21 days ago
    Ermias, 

    This formula should work as well.  You are always just going three weeks after the previous Wednesday, unless it is a Wednesday, then you are just going three week from that day.  

    if(DayofWeek([Assignment Date] !=3,
    PrevDayofWeek([Assignment Date], 3) + Days(21),
    [Assignment Date] + Days(21))

    ------------------------------
    Brandon Drake
    JHI Group
    Monroeville OH
    ------------------------------