Skip to main content
Solved

Making date last Thursday of each month

S

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!

Best answer by Kamille_Parks11

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'
)
View original
Did this topic help you find an answer to your question?

2 replies

Kamille_Parks11
Forum|alt.badge.img+25

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'
)
S
B
K
7 people like this
  • S
    Sarah_Roach
  • B
    Blossoms_Office
  • K
    KoaTechAdmin
  • TheTimeSavingCo
    TheTimeSavingCo
  • W
    Wenjie_Xu
  • Justin_Barrett
    Justin_Barrett
  • F
    Furst_Name
S
  • Sarah_Roach
  • Author
  • New Participant
  • 1 reply
  • August 2, 2021
Kamille_Parks11 wrote:

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


Most helpful members this week

Terms & Conditions