Help

Making date last Thursday of each month

Topic Labels: Formulas
Solved
Jump to Solution
1606 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Sarah_Roach
5 - Automation Enthusiast
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(
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
Kamille_Parks
16 - Uranus
16 - Uranus

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

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!