5 Replies Latest reply on Feb 9, 2017 10:04 PM by danielfarnan

    Need Custom Function

    coherentkris

      I need a custom function that will produce week numbers between a pair of dates.

      For example pair of dates

      end date - Feb 9 2017 (week 6)

      start date -  Dec 1 2016 (week 49)

      Would return List ( 6,5,4,3,2,1,52,51,50,49)

      Brain dead after 12 hours today.

      TIA.

      Kris

        • 1. Re: Need Custom Function
          user19752

          Don't you need 53?

           

          And, week 53 has 7 days in 2016, but only a Sunday in 2017. Same case will occur for week 54 in leap year.

           

          How do you use the result?

          • 2. Re: Need Custom Function
            user19752

            First try with suspecting you need max one year of date range (otherwise result have duplicated numbers)

             

            List up every WeekOfYear(Sunday in range)

             

            Let ( [

            end = Date ( 2 ; 9 ; 2016 ) ;

            start = Date ( 12 ; 26 ; 2015 ) ;

            $d = Div ( end ; 7 ) * 7 ;

            numW = Div ( end ; 7 ) - Div ( start ; 7 ) ;

            w = "&Char(13)&Let($d=$d-7;WeekOfYear($d))" ;

            f = WeekOfYear ( end ) & If ( numW > 0 ; Substitute ( 10 ^ numW - 1 ; 9 ; w ) )

            ] ;

            Evaluate ( f )

            )

             

            result

            7

            6

            5

            4

            3

            2

            53

            52

             

            This lacks 1 in result, since the first week of 2016 is same week as last week of 2015.

            • 3. Re: Need Custom Function
              philmodjunk

              Two custom functions, one the subfunction of the other:

               

              CF #1:

              WeeksOfYear ( StartDate ; EndDate ) =

              //WeeksOfYear ( StartDate ; EndDate )

               

              //Returns comma delimitted list of weeks of year from EndDate to StartDate (order is reversed)

              //Uses subfunction WeeksOfYearList to get return delimitted list and to avoid third parameter used to build list

               

              Substitute ( WeeksOfYearList ( StartDate ; EndDate ; "" ) ; ¶ ; ", " )

               

              CF#2:

              WeeksOfYearList ( StartDate ; EndDate ; TheList ) =

               

              //WeeksOfYearList ( StarDate ; EndDate ; TheList )

               

              //Produces Return delimitted list of weeks of year from EndDate to StartDate (Order is reversed)

               

              //Sub function called by WeeksOfYear

               

              Let ( [ w = WeekOfYear ( EndDate ) ] ;

                       Case ( EndDate < StartDate ; "" ;

                                 IsEmpty ( FilterValues ( w ; TheList ) ) ; List ( w ; WeeksOfYearList ( StartDate ; EndDate - 1 ; List ( w ; TheList ) ) ) ;

                                 WeeksOfYearList ( StartDate ; EndDate- 1 ; List ( w ; TheList ) )

                               ) //Case

                     ) // Let

              • 4. Re: Need Custom Function
                danielfarnan

                Untested:

                 

                Let (

                [

                _endWeek = WeekOfYear ( pEndDate )

                ] ;

                 

                Case (

                pEndDate < pStartDate ; "" ) ;

                List ( _endWeek ; cfWeeks ( pStartDate ; pEndDate - 7 ) )

                )

                 

                )

                • 5. Re: Need Custom Function
                  danielfarnan

                  So perhaps just run it through something like:

                   

                  Substitute ( _result ; "53" ; "1¶53" )

                   

                  afterwards?