Forum Discussion

Del's avatar
Del
Qrew Cadet
6 years ago

Formula date field third Wednesday

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
------------------------------
Formulas and functions
  • BrandonDrake's avatar
    BrandonDrake
    Qrew Cadet
    6 years 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
    ------------------------------
    • Del's avatar
      Del
      Qrew Cadet
      6 years ago
      Thanks for the help. The Assignment date can be any given day of the week

      ------------------------------
      Ermias Bean
      ------------------------------
      • Del's avatar
        Del
        Qrew Cadet
        6 years ago
        Today just happens to be Wednesday.

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