5 Replies Latest reply on Apr 18, 2013 6:55 AM by nmorales

    Figuring out week number within a specific month

    nmorales

      Hi All

       

      I was wondering if anyone knew how to calculate the amount of weeks within one month. My goal is to able to determine the date of the first friday of each month

        • 1. Re: Figuring out week number within a specific month
          taylorsharpe

          Let ( [

           

           

          F1 = [Insert Date Field - it can be any date in the month you are searching such as Date ( 1 ; 1 ; 2013 ) ] ;

          MM = Month ( F1 ) ;

          YYYY = Year ( F1 ) ;

          F2 = DayOfWeek ( F1 ) ;

          F3 = Case (

                    F2 = 6 ; 1 ;

                    F2 = 5 ; 2 ;

                    F2 = 4 ; 3 ;

                    F2 = 3 ; 4 ;

                    F2 = 2 ; 5 ;

                    F2 = 1 ; 6 ;

                    F2 = 7 ; 7 )

           

          ] ;

           

          Date ( MM ; F3 ; YYYY )

           

          )

          • 2. Re: Figuring out week number within a specific month
            Mike_Mitchell

            How about:

             

            Let ( [

            firstDate = Date ( {month} ; 1 ; {year} )

            ] ;

             

            firstDate - dayofweek ( firstDate ) + 6

             

            )

             

            Edit: I realized this morning there's an error in this. If the first day of the month is a Saturday, this calc will fail. So here's a corrected version:

             

            Let ( [

            firstDate = Date ( {month} ; 1 ; {year} ) ;

            weeksDay = DayOfWeek ( firstDate )

            ] ;

             

            Case ( weeksDay < 7 ; firstDate - dayofweek ( firstDate ) + 6 ; firstDate + 6 )

             

            )

             

            Message was edited by: Mike_Mitchell

            • 3. Re: Figuring out week number within a specific month
              DrewTenenholz

              nmorales --

               

              Here's  my version of the same calculation, almost identical to Taylor's.  The 'trick' here is to figure out what day of the week the first day of the specified month is, then add enough days to get to a Friday.  All of the suggestions are doing that, just in slightly different ways.  I've tried to add comments so you can see what I intended.

               

              I like to use Choose (0 index; 1 index; 2 index ; ) with dates, it's very fast and compact to type.  A CASE () is just as good, and Mike skipped all that by just doing simple arithmetic.

               

              I started in December, just to show that FileMaker's date engine will do very useful calculations for you, so you don't need to worry about what happens from Dec. to Jan.

               

              -- Drew Tenenholz

               

               

              Let ( [

              start= Date ( 12 ; 15 ; 2013 ) // whatever your starting date should be

              ; thisMonth= Month ( start )

              ; thisYear= Year ( start )

               

              ; newDate= Date ( thisMonth + 1 ; 1 ; thisYear ) // Date ( month ; day ; year )  i.e. the first day of the next month

              ; theDayOfWeekForTheFirst= DayOfWeek ( newDate )  // a number from 1 -7

              ; offset= Choose ( theDayOfWeekForTheFirst ; 0 ; 5 ; 4 ; 3 ; 2 ; 1 ; 0 ; 6 )  // figure out how many days to add to the 1st to get a Friday

               

              ; firstFriday= Date ( thisMonth + 1 ; 1 + offset ; thisYear )

               

              ; result= DayName ( firstFriday ) & ", " & Day ( firstFriday ) & " " & MonthName ( firstFriday ) & " " & Year ( firstFriday )

               

              ];

              result

               

              • 4. Re: Figuring out week number within a specific month
                nmorales

                This is helpful however can you please explain this line

                 

                 

                offset= Choose ( theDayOfWeekForTheFirst ; 0 ; 5 ; 4 ; 3 ; 2 ; 1 ; 0 ; 6 )  // figure out how many days to add to the 1st to get a Friday

                ; firstFriday= Date ( thisMonth + 1 ; 1 + offset ; thisYear )

                I've never used the Choose function but I looked it up and it seems that if the Day is 1 offset would be 5 if day 2 offset is 4? Correct?

                 

                 

                So if the first day of the next month is a Wednesday which would return 4 right you would add 2 to get Friday?    What if you dont want the first friday. Instead you want the first Monday.  Would I create different offsets based on which day they choose?

                 

                 

                And if they want the second Friday or Monday I would create offsets for that as well?

                 

                so they would be as follows:

                 

                offsetSecondFriday = CHoose (  theDayOfWeekForTheFirst ; 0 ; 12 ; 11 ; 10 ; 9 ; 8 ; 7 ; 13 )

                 

                offsetFirstMonday= Choose(  theDayOfWeekForTheFirst ; 0 ; 1 ; 0 ; 6 ; 5 ; 4 ; 3 ; 2 )

                 

                Message was edited by: nmorales

                • 5. Re: Figuring out week number within a specific month
                  nmorales

                  I tried what I thought and it worked! Thank you so much.  Now I need to figure out the last week of each month. You see Im trying to make it like outlook select the First, Second Third, Fourth or Last Monday Tues Wed Thurs Fri Sat of each month. If you have any ideas. Feel free.

                   

                  Thanks!