Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Making date last Thursday of each month

Topic Labels: Formulas
Solved
Jump to Solution
527 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

1 Solution

Accepted Solutions

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'
)

See Solution in Thread

2 Replies 2

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!