6 Replies Latest reply on Nov 28, 2016 10:00 AM by okramis

    Offsetting dayofweek

    jasonperl

      I am trying to run a search for next week (Monday through Sunday) but since Filemaker starts the week on Sunday (day 1), I am having difficulty getting the results when running the script on Sundays. This is my calculation, but I am uncertain how to put in the offset. Any help would be awesome! Thanks.

       

      ( Get ( CurrentDate ) – DayOfWeek ( Get ( CurrentDate ) ) + 9 ) & “…” & ( Get ( CurrentDate ) + 15 – DayOfWeek ( Get ( CurrentDate ) ) )

        • 1. Re: Offsetting dayofweek
          David Moyer

          Hi,

          here's a custom function that returns the date of the last day of the week.  This one has been adjusted for the Monday-Sunday week.

          Use this to find the last day of the week, then subtract seven six for the first day of the week.

          Capture.PNG

          • 2. Re: Offsetting dayofweek
            Malcolm

            Use a Let statement to make it easy to set out the different components:

             

            Let (
                [
                      firstDayOfWeek = 2            /* Sun = 1, Mon = 2, etc */
                    ; aDate = get(currentdate)
                    ; daysUntilNextWeek =  ( 7 + firstDayOfWeek ) - dayofweek( aDate )
                    ; NextWeekStart = aDate + daysUntilNextWeek
                    ; NextWeekEnd = NextWeekStart + 6
                ];
            
                getastext( NextWeekStart ) & "..." & getastext( NextWeekEnd )
            
            )
            
            • 3. Re: Offsetting dayofweek
              okramis

              here's an other version:

               

              Let ( [

               

              _date = Get ( CurrentDate ) + 7 //date in 1 week

              ; _weekday = Choose ( DayOfWeek ( _date ) - 1 ; 6 ; 0 ; 1 ; 2 ; 3 ; 4 ; 5 ) //weekday cero-based (6 = sunday)

              ; _start = _date - _weekday //if today is sunday: _date - 6 = tomorrow

              ; _end = _start + 6

               

              ] ;

               

              _start & "…" & _end

               

              )

               

              best regards

              Otmar

              • 4. Re: Offsetting dayofweek
                Malcolm

                well spotted okramis, the end of week is six days forward, not seven

                • 5. Re: Offsetting dayofweek
                  jasonperl

                  Thanks Otmar and everyone.

                   

                  Otmar - would that completely replace my calculation? Just want to be sure I understand... your calculation will return next week (with the week starting Monday and ending sundae).

                   

                  Thanks again. This forum is always helpful!

                  • 6. Re: Offsetting dayofweek
                    okramis

                    Yes, it should work as intended. Just put it in the data viewer and set the _date to several test-dates (leave the + 7 behind).

                    If you define the start of next week if lets say today is sunday as the following day means next day, the formula should be correct, if it should be a week later, just replace "+ 7" with "+ 14".

                     

                    best regards

                    Otmar