Aug 01, 2021 10:14 AM
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!
Solved! Go to Solution.
Aug 01, 2021 05:54 PM
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'
)
Aug 01, 2021 05:54 PM
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'
)
Aug 02, 2021 07:20 AM
This is perfect! Thank you so much!