AnsweredAssumed Answered

How to solve Bi-weekly Range of Dates Spanning Year-End

Question asked by PeterDoern on Apr 13, 2018
Latest reply on Apr 15, 2018 by fmpdude

I've been scratching my head over this one; I'm 80% there but after a long night could use some help.

 

I'm setting up a function that will allow users to generate a list of dates using the following criteria:

 

start date

end date

day(s) of week (e.g. "234" would return Mondays, Tuesdays and Wednesdays)

frequency (e.g. weekly or bi-weekly)

 

I'm using Agnès Barouh's excellent, should-be-a-native-FileMaker-function custom function, CustomList ( start ; end ; function ).

 

Here's what I have so far:

 

Let ( [

     ~start = GetAsNumber ( Date ( 4 ; 1 ; 2016 ) ) ; // test start date

     ~end = GetAsNumber ( Date ( 4 ; 1 ; 2017 ) ) ; // test end date

 

    
     ~dow = "2" ; // testing with just Mondays for now. Change to "1234567" for an entire week, for example

     ~frequency = "bi-weekly" ; // testing just bi-weekly for now

     ~startWeek = WeekOfYear ( ~start ) ;

     ~weekMod = Mod ( ~startWeek ; 2 ) ;

 

 

     ~function =

          "Let ( [

               ~date = GetAsDate ( [n] ) ;

               ~dow = \"" & ~dow & "\" ;

               ~frequency = \"" & ~frequency & "\" ;

               ~startWeekMod = " & ~weekMod & " ;

               ~thisWeekMod = Mod ( WeekOfYear ( ~date ) ; 2 )

          ] ;

               Case (

                    isEmpty ( Filter ( ~dow ; DayOfWeek ( ~date ) ) ) ;

                         \"\" ;

                    ~frequency = \"bi-weekly\" and ~thisWeekMod ≠ ~startWeekMod ;

                         \"\" ;

                    ~date

          )

     )"

] ;

     CustomList ( ~start ; ~end ; ~function )

)

 

For the most part this works like a charm. However, on spanning certain years the Mod ( WeekOfYear ; 2 ) resets and throws off the alternating weeks.

 

For example, bi-weekly Mondays from April 16, 2018 to April 15, 2018 properly jumps from December 24, 2018 to January 7, 2019.

 

But, if the date range is April 11, 2016 to April 27, 2017, the routine leaps from December 19, 2016 to January 9, 2017. It should go from December 19, 2016 to January 2, 2017 and then bi-weekly from there.

 

I'm looking for an elegant way to handle these exceptions, and I hope some brilliant mathematician out there can help me.

 

I've attached a sample file.

Attachments

Outcomes