Formula to return 3rd Thursday of the month

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I'm looking to write a formula to return the third Thursday of the current month... is this possible?

Photo of Jennifer

Jennifer

  • 10 Points

Posted 3 years ago

  • 0
  • 1
Xavier Fan, another QuickBase Solution Provider answer the same Question - only for the Third Friday of the month.  here https://quickbase-community.intuit.com/questions/1369075-i-need-a-formula-to-calculate-a-date-based-on-a-start-date-that-results-in-the-first-or-third-friday-of-the-month

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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
Try this:

var Number ORDINAL = 3;
var Number DOW = 4;

var Date FDOM = FirstDayOfMonth([DateIn]);
var Number OFFSET = Mod($DOW - DayofWeek($FDOM), 7);

$FDOM + Days(7 * ($ORDINAL - 1) + $OFFSET)

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.
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
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
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
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.
Photo of Jennifer

Jennifer

  • 10 Points
Thank you Dan!  Very helpful!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
I learned this formula from Ada over at the Pokemon Math Learning League.