4 Replies Latest reply on Dec 22, 2009 9:52 AM by Steve_I

    How Can I Find the Date of the "Xth Week-Day" of the Month?

    Steve_I

      Title

      How Can I Find the Date of the "Xth Week-Day" of the Month?

      Post

      Does anyone have a way to write a calculation which returns a date every month for a specification of the Xth day of the week each month... for example: "The Third Thursday of the Month" or "The Last Friday of the Month"?  I'm using FMP Advanced v 10 (windows) on a FMS 10 served solution.

       

      Suggestions are greatfully accepted!

       

      Thanks, and Happy Holidays

        • 1. Re: How Can I Find the Date of the "Xth Week-Day" of the Month?
          philmodjunk
            

          There are several variations of the following expression possible. I'm assuming you have a number field, DayOfWeek, for the day of the week (Sunday = 1, Monday = 2...) and a 2nd number field, Multiple, to specify the first, second, etc day of the month. If you don't want this for the current month, you'll need to alter it a bit:

           

          Let ( [ today = Get ( CurrentDate ) ; 

                    FirstDay = Date ( Month(today) ; DayofWeek - DayOfWeek(today) - 7 * (DayofWeek  ≥ DayOfWeek(today) )  ; Year (today) ) ] ;
                  FirstDay + 7 * Multiple )

          • 2. Re: How Can I Find the Date of the "Xth Week-Day" of the Month?
            comment_1
               Another ten minutes I'll never get back...
            • 3. Re: How Can I Find the Date of the "Xth Week-Day" of the Month?
              philmodjunk
                

              Did some more checking with this expression and discovered it was off by one day.

               

              Let ( [ today = Get ( CurrentDate ) ; 

                        FirstDay = Date ( Month(today) ; DayofWeek - DayOfWeek(today) - 7 * (DayofWeek  ≥ DayOfWeek(today) )  ; Year (today) ) ] ;
                      FirstDay + 7 * Multiple + 1)

               

              I keep thinking that there's a more elegant way to set this up, but haven't worked that out. Also note that multiples of 5 may produce a date for the following month and multiples greater than 5 will always produce a date in the following month.

               

              Note also that this expression should be unstored if it's used as the definition of a calculation field.

              • 4. Re: How Can I Find the Date of the "Xth Week-Day" of the Month?
                Steve_I
                  

                Thanks!  This is in line with what I had been trying, except for the syntax error I realized I was making.  If there's something more elegant, I haven't figured it out yet.  I actually half expected to fine a ready-made function for this, because I would think that a lot of scheduling applications require this type of logic.

                 

                Thanks again for the assist!