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 ------------------------...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew 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
------------------------------
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
------------------------------
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
------------------------------
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
------------------------------
- While 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
------------------------------