I'm looking to write a formula to return the third Thursday of the current month... is this possible?
 10 Points
Posted 3 years ago
QuickBaseCoach App Dev./Training, Champion
 65,422 Points
Xavier Fan, another QuickBase Solution Provider answer the same Question  only for the Third Friday of the month. here https://quickbasecommunity.intuit.com/questions/1369075ineedaformulatocalculateadatebasedonastartdatethatresultsinthefirstorthirdfridayofthemonth
I suggest that you go to that question and let Xavier know that he would have your undying gratitude if he would post a response to your Thursday Question and provide a link to it. I could probably play with his code to get it working, but maybe he will find the time to answer as the code adjustments are probably more obvious to Xavier.
I suggest that you go to that question and let Xavier know that he would have your undying gratitude if he would post a response to your Thursday Question and provide a link to it. I could probably play with his code to get it working, but maybe he will find the time to answer as the code adjustments are probably more obvious to Xavier.
Ⲇanom the ultimate (Dan Diebolt), Champion
 30,044 Points
Try this:
var Number ORDINAL = 3;You should be able to change the ORDINAL (eg 1 for 1st, 2 for 2nd 3 for 3rd, 4 for 4th) and DOW (0 for Sunday, 1 for Monday ... 6 for Satruday etc) to suit your needs.
var Number DOW = 4;
var Date FDOM = FirstDayOfMonth([DateIn]);
var Number OFFSET = Mod($DOW  DayofWeek($FDOM), 7);
$FDOM + Days(7 * ($ORDINAL  1) + $OFFSET)
Xavier Fan, Champion
 410 Points
Here's a general version of my formula from the other thread. Dan's version is much more compact.
[Date] is the input date. If you want it always for the current month, you can use Today() instead of [Date].
var Number DateDesiredDayofWeek = 4; // 0 for Sun, 1 for Mon, 2 for Tues, etc.
var Number DateDesiredWeekNumber = 3; // e.g. 1 for 1st Thurs, 2 for 2nd Thursday, etc.
var Date FirstDayCurrentMonth = FirstDayOfMonth( [Date] );
var Number DayOfWeekFirstDayCurrentMonth = DayOfWeek( $FirstDayCurrentMonth );
var Number DaysToFirstDateDesiredCurrentMonth = Case ($DayOfWeekFirstDayCurrentMonth,
$DateDesiredDayofWeek + 1, 6,
$DateDesiredDayofWeek  $DayOfWeekFirstDayCurrentMonth);
var Date FirstDateDesiredCurrentMonth = $FirstDayCurrentMonth + Days( $DaysToFirstDateDesiredCurrentMonth );
var Date DateDesiredCurrentMonth = $FirstDateDesiredCurrentMonth + Days( 7 * ($DateDesiredWeekNumber  1) );
$DateDesiredCurrentMonth
[Date] is the input date. If you want it always for the current month, you can use Today() instead of [Date].
var Number DateDesiredDayofWeek = 4; // 0 for Sun, 1 for Mon, 2 for Tues, etc.
var Number DateDesiredWeekNumber = 3; // e.g. 1 for 1st Thurs, 2 for 2nd Thursday, etc.
var Date FirstDayCurrentMonth = FirstDayOfMonth( [Date] );
var Number DayOfWeekFirstDayCurrentMonth = DayOfWeek( $FirstDayCurrentMonth );
var Number DaysToFirstDateDesiredCurrentMonth = Case ($DayOfWeekFirstDayCurrentMonth,
$DateDesiredDayofWeek + 1, 6,
$DateDesiredDayofWeek  $DayOfWeekFirstDayCurrentMonth);
var Date FirstDateDesiredCurrentMonth = $FirstDayCurrentMonth + Days( $DaysToFirstDateDesiredCurrentMonth );
var Date DateDesiredCurrentMonth = $FirstDateDesiredCurrentMonth + Days( 7 * ($DateDesiredWeekNumber  1) );
$DateDesiredCurrentMonth
Ⲇanom the ultimate (Dan Diebolt), Champion
 30,044 Points
XF》... much more compact
It is a one liner in Javascript using either the momentjs or datejs libraries (both of which QuickBase uses). This is all the more reason to wholesale replace the formula language with JavaScript.
It is a one liner in Javascript using either the momentjs or datejs libraries (both of which QuickBase uses). This is all the more reason to wholesale replace the formula language with JavaScript.
Ⲇanom the ultimate (Dan Diebolt), Champion
 30,044 Points
I learned this formula from Ada over at the Pokemon Math Learning League.
Related Categories

Formulas & functions
 2927 Conversations
 75 Followers