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
527 2
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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(
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
16 - Uranus

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

``````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'
)
``````
2 Replies 2
16 - Uranus

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

``````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'
)
``````
5 - Automation Enthusiast

This is perfect! Thank you so much!