Skip to main content

Hi!


I am trying to set up a field to automatic track the day of a meeting. This meeting is the last Thursday of each month. I found a formula to automatically populate the last day of each month but I can’t find/figure out a solution to change that date to the last Thursday.


For Example Last day of the month for August is 8/31/2021 (Tuesday) but I need it to be 8/26/2021 (The last Thursday)


Here is the formula I found for the last day of the month:


DATETIME_FORMAT(

DATEADD(

DATETIME_PARSE(

DATETIME_FORMAT(

TODAY(),

‘YYYY’

) & ‘-’ &

(VALUE(

DATETIME_FORMAT(

TODAY(),

‘MM’

)

)

+ 1)

& ‘-01’,

‘YYYY/MM/DD’

) , -1, ‘day’

)

, “MM/DD/YY”

)


I was thinking another formula field using WEEKDAY() might work but it just gives an error when trying to change the day. Thank you in advance!

The formula for the last day of the month seems like overkill. The same formula, simplified looks like this:


DATEADD(

   DATEADD(

      DATETIME_PARSE(DATETIME_FORMAT(TODAY(),'YYYY-MM-01')),

      1,

      'month'

   ),

   -1,

   'day'

)

(Turn off “show same timezone” in the field config options)


To get the last Thursday of the month, you would in fact need the WEEKDAY() function in conjunction with DATEADD() to add (or rather subtract) a certain number of days from the last day of the month depending on that date’s weekday. Weekdays are numbered from 0 to 6 where 0 is Sunday, 6 is Saturday, and Thursday is 4. Therefore:


DATEADD(

   {Last of Month}, 

   SWITCH(

      WEEKDAY({Last of Month}), 

      0, -3, 

      1, -4, 

      2, -5, 

      3, -6, 

      4, 0, 

      5, -1, 

      6, -2

   ), 

   'days'

)

The formula for the last day of the month seems like overkill. The same formula, simplified looks like this:


DATEADD(

   DATEADD(

      DATETIME_PARSE(DATETIME_FORMAT(TODAY(),'YYYY-MM-01')),

      1,

      'month'

   ),

   -1,

   'day'

)

(Turn off “show same timezone” in the field config options)


To get the last Thursday of the month, you would in fact need the WEEKDAY() function in conjunction with DATEADD() to add (or rather subtract) a certain number of days from the last day of the month depending on that date’s weekday. Weekdays are numbered from 0 to 6 where 0 is Sunday, 6 is Saturday, and Thursday is 4. Therefore:


DATEADD(

   {Last of Month}, 

   SWITCH(

      WEEKDAY({Last of Month}), 

      0, -3, 

      1, -4, 

      2, -5, 

      3, -6, 

      4, 0, 

      5, -1, 

      6, -2

   ), 

   'days'

)

This is perfect! Thank you so much!

Reply


Terms & Conditions