Linking a week rota system to a date

[Elmobram22]

Hi,

I'm just wondering if this would be possible. I have a staff rota system that works on a rolling 4 weekly basis. I am using a table to store the shifts of a person dependent on week. I want to be able to tell access that Monday on week 1 corresponds to a certain date and then get access to figure out the rolling system based on that date.

eg Monday 21/7/13 is week 1 (7 days later it knows it is linked to week 2)

This is so if a staff member is off sick I can say they were off sick on the 24th and it will populate their timesheet with the corresponding shift without me having to input it manually. Doable?

Cheers,

Paul

 

[namliam]

yeah doable, you can use datepart or format function to find the "real" week of the date.
Then substract (in this case) 30 weeks to get to week 0 (or add 1 and get to week 1)
Then you get weeks 1,2,3,4,5,6,7,8,etc then devide by 4 and multiply the remainder by 4 to get 1,2,3,4,1,2,3,4,1,2,3,4 etc from your weeknumbers...

Challanges start comming from year changes... perhaps its better to work with the dates and day/7 differences instead... hmz...