Forum Discussion
PeteJames
4 years agoQrew Cadet
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
------------------------------
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__You4 years agoQrew LegendI 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
------------------------------- TateForgey3 years agoQrew Assistant CaptainThis 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
------------------------------- TateForgey3 years agoQrew Assistant CaptainWhile I'm conceptualizing how to fix delta, I'm inserting a blunt force attack:
Case(DayOfWeek( (FirstDayOfMonth(Today()))),
0, FirstDayOfMonth(Today()) + Days(9),
1, FirstDayOfMonth(Today()) + Days(8),
2, FirstDayOfMonth(Today()) + Days(7),
3, FirstDayOfMonth(Today()) + Days(13),
4, FirstDayOfMonth(Today()) + Days(12),
5, FirstDayOfMonth(Today()) + Days(11),
6, FirstDayOfMonth(Today()) + Days(10)
)
------------------------------
Tate Forgey
------------------------------