6 Replies Latest reply on Jun 22, 2010 8:37 AM by Pam

    Exclude Friday from calculated date

    Pam

      Title

      Exclude Friday from calculated date

      Post

      I am a novice user of FileMaker (v Pro 10) on a Mac. (have used neither before).

      I need to calculate a date 10 days from 'start date', which is a field in a table. If the result is a Friday, then the calculation should be 'start date' + 11. Tried to do this by means of an if statement, but can't figure out the correct wording to exclude Fridays.

      I am in Dubai in the UAE, so unsure if Friday is day 6 or not.

      Would be grateful for any assistance, I am under pressure to get the DB up and running.

      Thanks P

        • 1. Re: Exclude Friday from calculated date
          raybaudi

          Hi

           

          try this one:

           

          Let(
          end = start date + 10 ;
          Case(
          DayOfweek( end ) = 6 ; end + 1 ;
          end
          )
          )

          that can be simplified to:

           

          start date + 10 + ( DayOfWeek( start date + 10 ) = 6 )

           

          • 2. Re: Exclude Friday from calculated date
            Pam

            HI Daniele,

             

            Nothing wrong with your English, but  obviously something wrong with my brain.

             

            I tried this:

             

            Let (Services::ServiceDate = Contracts::StartDate + 10; Case ( DayOfWeek (Services::ServiceDate) = 6, Services::ServiceDate + 1 ; Contracts::StartDate + 10 )))

             

            I get an error message at :: A number, text constant, field name or "(" is expected here.

             

            I am working with fields from 2 different tables. T Services F ServiceDate and T Contracts F StartDate

             

            What to do?

            • 3. Re: Exclude Friday from calculated date
              raybaudi

              A field name can't be a variable name !

               

              Try with:

               

              Let ( E = Contracts::StartDate + 10 ; E + ( DayOfWeek ( E ) = 6 ) )

               

              The field holding this calculation must be into the Contracts table.

              • 4. Re: Exclude Friday from calculated date
                Pam

                Hi Daniele,

                 

                The field I am trying to calculate sits in the 'Services' table. The field [services].[service date] is calculated 10 days from the field [contract].[start date], which is in the 'Contracts' table. As Friday is the 'day off' here, therefore, if the 10th day falls on a Friday, the service must be the following day.

                 

                Would this mean that the proposed solution would be ineffective?

                 

                Sorry to be such a pest, your kind assistance is much appreciated.

                • 5. Re: Exclude Friday from calculated date
                  raybaudi

                  Pam wrote:

                   

                  The field I am trying to calculate sits in the 'Services' table.


                   

                  Why it can't stay in the "Contracts table ?

                   


                  • 6. Re: Exclude Friday from calculated date
                    Pam

                    Was just the way I had set up the tables.

                    However, I am pleased to say that you put me on the right track.  I now have a script that takes the service date, calculated from the contracts table, checks to see if the result is a friday, and if so, changes it.