6 Replies Latest reply on Sep 4, 2018 6:45 AM by Shinichiro Murakami

Last Thursday of the month - almost got it

I'm working on a calc that shows when the next scheduled meeting is, based on the value of today.   These meetings fall on the last Thursday of every month.

This formula does the trick, EXCEPT when [TODAY] (which is defined as TODAY()) is on a date greater than the last Thursday in the same month.   In other words, for the entire month of August this formula displays as 8/30, but on 8/31 I want it to show the September meeting date.

DATE(IF month([Today]) = 2 then datetrunc('week',datetrunc('month',[Today])+27,'Thursday')

ELSE
datetrunc('week',datetrunc('month',[Today])+30,'Thursday')

END)

Any suggestions?

* Bonus points if you can help me figure out how to move the meeting one week earlier if the last Thursday of the month falls on Thanksgiving or Christmas.

Thanks...

• 1. Re: Last Thursday of the month - almost got it

Hi Bill,

You can try below approach:

1. Last day of the month =  DATE(datetrunc('month',dateadd('month',1,[Order Date]))-1)

2. name of last day = DATENAME('weekday',[Last day of the month])

3. day = DATEPART('day',[Last day of the month])

4. last thursday day =

CASE [name of last day]

WHEN "Sunday" then [day]-3

WHEN "Monday" then [day]-4

WHEN "Tuesday" then [day]-5

WHEN "Wednesday" then [day]-6

WHEN "Thursday" then [day]

WHEN "Friday" then [day]-1

WHEN "Saturday" then [day]-2

END

5. Last thursday date =  MAKEDATE(year([Last day of the month]),month([Last day of the month]),[last thursday day])

1 of 1 people found this helpful
• 2. Re: Last Thursday of the month - almost got it

Bill,

I have created couple of calculated field as :

today1 same as yours:

DATE(IF month([Today]) = 2 then datetrunc('week',datetrunc('month',[Today])+27,'Thursday')

ELSE

datetrunc('week',datetrunc('month',[Today])+30,'Thursday')

END)

next month as

today2 as :

if [Today]<=[today1] then [today1] else

(DATE(IF month([next month]) = 2 then datetrunc('week',datetrunc('month',[next month])+27,'Thursday')

ELSE

datetrunc('week',datetrunc('month',[next month])+30,'Thursday')

END))

end

You have to use this today2 now as final field.

You can try simplifying the calculations

Hope it helps.

2 of 2 people found this helpful
• 3. Re: Last Thursday of the month - almost got it

For last part

* Bonus points if you can help me figure out how to move the meeting one week earlier if the last Thursday of the month falls on Thanksgiving or Christmas.

you create one more field as :

if

(month([today2])  = 12 and day([today2])=25)

OR

[today2]=[some logic to identify thanksgiving day]

THEN [today2]-7

else

[today2]

end

1 of 1 people found this helpful
• 4. Re: Last Thursday of the month - almost got it

The bonus is getting quite a bit complicated, because it get into circular reference of date and meeting date.

I mean if you pull in the meeting date by 7 days, target next meeting date of

days between "original meeting date -7" to "original meeting date-1"

also need to be pushed out by one month.

The simplest approach from my investigation is to prepare two target meeting date for respective date.

Then pick 2 if date > pick 1.

you can replace [Date] with [parameter date] or today().

Thanks,

Shin

2 of 2 people found this helpful
• 5. Re: Last Thursday of the month - almost got it

Thanks to all for your assistance...much appreciated

• 6. Re: Last Thursday of the month - almost got it

HI Bill,

Please mark either of answer  as correct to close the thread, not from inbox but from original post.

Thanks,

Shin