Calculate week Number from a 4 week cycle

[catbert]

Hi,

I am dealing with schedules which vary based on a four week rolling programme. I neeed to display which week we are currently on from current date - and calulate what week exisitng data was collected based on a date.

I have used DATEPART to show the week number from the first full week in Jan 06 but I can't work out a simple formula to convert the week number into 1,2,3 or 4.

For example week commencing 20th Feb is the 8th week - but as part of our rolling cycle it is week 4.

Any help gratefully recieved - thanks!

 

[PHV]

Have a look at the Mod operator.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

You want to look at the DAY of the month

Days 1 - 7 should be Week 1.

So

WeekNum = Int((Day([MyDate])-1)/7)+1

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[catbert]

Thanks for the replies - Skip I can't use the day as day 1 to 7 isn't necessarily in the first week - Week 1 in January starts on the 2nd for instance.

PH I was thinking of using INT or Mod but had a mental block - as you pushed in the right direction I have come up with the solution - WeekNumber MOD 4, gives me what I need

Thanks Again
CB

 

[SkipVought]

Then use DATEPART to find the starting week for a date. Then use the DAY OFFSET to calculate the week nbr as I described.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]