Forum Discussion

PrashantMaheshw's avatar
PrashantMaheshw
Qrew Captain
3 years ago

Calculate next 2nd Tuesday of a month

I have a task due to submit every 2nd Tuesday of month , for life of me I can't figure how to calculate next instance .

------------------------------
Prashant Maheshwari
------------------------------
Formulas and functions

6 Replies

Sort By
  • MichaelTamoush's avatar
    MichaelTamoush
    Qrew Captain
    3 years ago
    I don't have time to play around with it right now, but I have this in my notes. Not your use case but maybe you can reverse engineer it and use it to come up with a solution? Though, I can't help but think there is a much easier way. Lets hope @Mark Shnier (YQC) can come to the rescue!

    My Notes:

    Calculate the previous Thursday on or before a date (in a due date field). So for example, if the Due Date was a Monday, I wanted the date of the previous Thursday. No matter the due date, I wanted the date of the prior Thursday (or if the due date was a Thursday, then I want that Thursday).

    For Prior Wednesday: [Due Date] - Days(Rem(DayOfWeek([Due Date])+4,7))
    For Prior Thursday: [Due Date] - Days(Rem(DayOfWeek([Due Date])+3,7))
    For Prior Friday: [Due Date] - Days(Rem(DayOfWeek([Due Date])+2,7))
    etc.

    (Sunday would be + 7, Saturday would be +1)​​

    ------------------------------
    Michael Tamoush
    ------------------------------
    • PrashantMaheshw's avatar
      PrashantMaheshw
      Qrew Captain
      3 years ago
      Thank you Michael for your reply, I do not know how to use this logic to derive at second Tuesday of a month, But I will play with this.


      ------------------------------
      Prashant Maheshwari
      ------------------------------
  • PeteJames's avatar
    PeteJames
    Qrew Cadet
    3 years ago
    How about this.. where test date is the date you are using to identify the month

    var number firstday = DayOfWeek(FirstDayOfMonth([test date]));
    var number delta = 9 - $firstday;
    If ($delta < 2, FirstDayOfMonth([test date]) + Days($delta),FirstDayOfMonth([test date]) + Days($delta + 7) )

    ------------------------------
    Pete James
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Qrew Champion
      3 years ago
      I think that Pete has a great solution for the 2nd Tuesday of the month.

      But if you need the next 2nd Tuesday of the month (ie maybe the 2nd Tuesday of the month has already occurred) we can build on the formula like this.

      var date TestDate = Today()+ Days(0); //  you can adjust this Days(0) to test for different dates.

      var number firstdayofcurrentmonth = DayOfWeek(FirstDayOfMonth($TestDate));
      var number deltaOne = 9 - $firstdayofcurrentmonth;
      var date SecondTuesdayOfThisMonth = If ($deltaOne < 2, FirstDayOfMonth($TestDate) + Days($deltaOne),FirstDayOfMonth($TestDate) + Days($deltaOne + 7) );



      var number firstdayofnextmonth = DayOfWeek(AdjustMonth(FirstDayOfMonth($TestDate),1));
      var number deltaTwo = 9 - $firstdayofnextmonth;
      var date SecondTuesdayOfNextMonth = If ($deltaTwo < 2, AdjustMonth(FirstDayOfMonth($TestDate),1) + Days($deltaTwo),AdjustMonth(FirstDayOfMonth($TestDate),1) + Days($deltaTwo + 7) );

      If(Today() <= $SecondTuesdayOfThisMonth, $SecondTuesdayOfThisMonth, $SecondTuesdayOfNextMonth)

      ------------------------------
      Mark Shnier (YQC)
      mark.shnier@gmail.com
      ------------------------------
      • TateForgey's avatar
        TateForgey
        Qrew Assistant Captain
        3 years ago
        This is just what I need, Mark.  Thanks! However, as I create the formula date field and look at it today (November 10, 2021) it produces an incorrect value. The date it produces is November 16, 2021.  The second Tuesday of this month was November 9, 2021. The code should have given me the second Tuesday of next month (which it is calculating correctly). 

        I'm trying to unpack your code, but if you get to it first, do you know why this is and how we can fix it?

        ------------------------------
        Tate Forgey
        ------------------------------