4 Replies Latest reply on Aug 30, 2012 2:40 PM by jhaffner

    Improve "Last Monday" Calc

    jhaffner

      Hello,

       

      I am working on a time card solution for our company, and I want to auto-enter last Monday's date in the Week Starting field. If today is Sunday or Monday, I want it to show last Monday's date. If it is Tuesday or later, I want it to show this Monday's date. Using FMPA 11.

       

      Here's my existing calculaiton:

       

      Let ( [

       

       

      today = get ( currentdate ) ;

       

       

      daynum = DayOfWeek (today) ] ;

       

       

      case (

       

      daynum < 3 ; //if today is Sunday or Monday, go back to previous Monday

       

       

      today - 5 - daynum;

       

      today + 2 - daynum; //otherwise go back to current Monday

       

      ) // end CASE

       

       

      ) // end LET

       

      ----------

      This works now, but I have a feeling it could be more efficient. Is there a better way to do this?

       

      John

       

      edit to clean up formatting

        • 1. Re: Improve "Last Monday" Calc
          DrewTenenholz

          John --

           

          When using this kind of date offset, I tend to use something like:

           

          Let ( [

          today= Get ( CurrentDate )

          ; dayOfWeek= DayOfWeek ( today )

          ; offset= Choose ( dayOfWeek ; 0 ; -6 ; 0 ; -1 ; -2 ; -3 ; -4 ; -5) // shift to most recent Monday

          ; newDate = today + offset

          ];

          newDate

          )

           

          This gives you a lot of flexibility, since you are specifying how much to shift the final date for each individual day of the week.

           

          Don't forget that the Choose() function is ZERO indexed, so there should be EIGHT values in the calculation, one for the zeroth element, one for the first, one for the second, etc.

           

          Hope this Helps,

          Drew Tenenholz

          1 of 1 people found this helpful
          • 2. Re: Improve "Last Monday" Calc
            jhaffner

            Hi Drew,

             

            I had seen something similar to this in searching on the forums here. I think I would have to make the Choose statement a bit different, since I want to go back to the previous Monday if today is Monday:

             

            Choose (dayofWeek; 0; -6; -7; -1...

             

            I had been trying to make the original into a one-line calc, but couldn't get my brain around the math.

             

            Thanks,

             

            John

            • 3. Re: Improve "Last Monday" Calc
              comment

              jhaffner wrote:

               

              I want it to show last Monday's date. If it is Tuesday or later, I want it to show this Monday's date.

               

              Try =

               

              Let ( d = Get ( CurrentDate ) ; d - Mod ( DayofWeek ( d ) - 3 ; 7 ) - 1 )

              • 4. Re: Improve "Last Monday" Calc
                jhaffner

                That's it!

                 

                I knew there had to be a way to do this without using the Choose function.

                 

                Thanks Michael!