4 Replies Latest reply on Nov 18, 2013 8:35 AM by ultranix

    Conditional date of the week

    ultranix

      Title

      Conditional date of the week

      Post

           I would like to have a calculation, that, depending on date entered, would calculate the 2nd Monday of the given month with a condition (explained later). (two fields - date_entered, date_calculated)

           1) So, if date_entered is 2013-11-05, it would return 2011-11-11,

           2) if the date has already passed this month's 2nd monday, then it would return 2nd monday of next month. so if date_entered is 2013-11-18, date_calculated would return 2013-12-09 (2nd monday of next month).

            

        • 1. Re: Conditional date of the week
          philmodjunk

               Let ( [ DT = YourDateFieldHere ;
                          M1st = DT - Day ( DT ) + 1 ;
                          Mon1 = M1st - DayofWeek ( M1st ) + 2 ; //Monday of 1st week
                          Mon2 = Mon1 + 7 + 7 * Mon1 < M1st ; //adjust 7 more days if Mon1 computes to be in last week of preceding month.
                          M1stb = Date ( Month (DT ) + 1 ; 1 ; Year ( DT ) ) ;
                          Mon1b = M1stb - DayofWeek ( M1stb ) + 2 ;
                          Mon2b = Mon1b + 7 + 7 * Mon1b < M1stb  //adjust 7 more days if Mon1b computes to be in last week of preceding month.
                        ];
                        IF ( Mon2 < DT ; Mon2 ; Mon2b )
                      ) // Let

               That's one approach. A recursive custom function could do it more simply. I have not had the time to test this calculation, however, so it may not work or need some debugging.

          • 2. Re: Conditional date of the week
            ultranix

                 I tested it, it doesn't work quite correctly.

                 I checked all expressions, 2 doesn't seem to work (returns 0) - "Mon2" and "Mon2b".

            • 3. Re: Conditional date of the week
              philmodjunk

                   Had to fix three things (in red):

                   Let ( [ DT = YourDateFieldHere ;
                              M1st = DT - Day ( DT ) + 1 ;
                              Mon1 = M1st - DayofWeek ( M1st ) + 2 ; //Monday of 1st week
                              Mon2 = Mon1 + 7 + 7 * ( Mon1 < M1st ) ; //adjust 7 more days if Mon1 computes to be in last week of preceding month.
                              M1stb = Date ( Month (DT ) + 1 ; 1 ; Year ( DT ) ) ;
                              Mon1b = M1stb - DayofWeek ( M1stb ) + 2 ;
                              Mon2b = Mon1b + 7 + 7 * ( Mon1b < M1stb )  //adjust 7 more days if Mon1b computes to be in last week of preceding month.
                            ];
                            IF ( Mon2 < DT ; Mon2b ; Mon2 )
                          ) // Let

              • 4. Re: Conditional date of the week
                ultranix

                     Thank you, perfect as always, Phil!