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

    Last Thursday of the month - almost got it

    bill.merlavage

      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
          meenu choudhary

          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
            Ankit Bansal

            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

            makedate(year(DATEADD('month',1,[Today])),month(DATEADD('month',1,[Today])),1)

             

            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
              Ankit Bansal

              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
                Shinichiro Murakami

                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
                  bill.merlavage

                  Thanks to all for your assistance...much appreciated

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

                    HI Bill,

                     

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

                     

                    Thanks,

                    Shin