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

# Need Custom Function

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

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

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

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

Untested:

Let (

[

_endWeek = WeekOfYear ( pEndDate )

] ;

Case (

pEndDate < pStartDate ; "" ) ;

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

)

)

• ###### 5. Re: Need Custom Function

So perhaps just run it through something like:

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

afterwards?