CF to return week number between two date range

[Andy Robo]

Hi All

There is proby an existign CF I could re-jig, but I want to create a function which returns the NUMBER of a WEEK from a startDate to myDate... ie, how many weeks between startDate and myDate

Something like

WeekOfTerm (startDate ; myDate)

startDate is the date in the first week and could be any day M-F (or even Sat or Sun)(by default if myDate = startDate then WeekOfTerm = 1... but I also want it to work so that if myDate falls in the same Mon-Sun week as startDate it also returns 1.

I want to fix it so that regardless of when the dates fall the function deals with whole weeks at a time.

Eg. if startDate falls on a thursday then if myDate = startDate //returns 1

if myDate = the Friday after, then still returns 1

however if myDate = the following monday I want it to return 2.

[comment]

I don't think you need a custom function for this. Try:

Let (

monStart = StartDate - DayOfWeek ( StartDate - 1 ) + 1

;

Div ( EndDate - monStart ; 7 ) + 1

)

[Andy Robo]

Hmmm, I got it to work okay if the starting day falls on a monday... thanks to Jason L. DeLooze of Nocturnal MicroSystems...

WeekOfTerm (StartDay ; StartMonth ; MyDate)

Let(

[

d = Date(StartMonth;StartDay;Year(MyDate)) ;

dp = Date(StartMonth;StartDay;Year(MyDate)-1) ;

s = d + Mod(StartDay-DayOfWeek( d )+3;7)-3 ;

sp = dp + Mod(StartDay-DayOfWeek( dp )+3;7)-3 ;

FY_Start = Case( MyDate >= s ; s ; sp )

] ;

Int( (MyDate - FY_Start)/7) + 1

)

Just don't know how to make it count part of a week as the first week...

Andrew

[Andy Robo]

Cheers Michael, your calc returns the number OF WEEKS between the two dates... but I want to calculate the ordinal number OF THE week, where the week of the startDate = 1, (where weeks are Mon - Sun)and so on...

Edited October 3, 2007 by Guest

[comment]

No, it returns the number of WHOLE weeks between the Monday on or before StartDate and EndDate, and adds 1 - I believe that is also the ordinal number you're looking for. If not, give an example where the result is different than expected.