472,353 Members | 1,411 Online

# Week of the month

Hi

I have a report which lists staff hours for various dates. What could be a
way to assign to each entry the week of the month that the date for the
entry belongs to?

Thanks

Regards
Nov 13 '05 #1
3 5041
Week of month defined as;

if date1 is date from the first entry whatever it is then
date1 - date1+6 = week 1
date1+7 - date1+13 = week 2

and so on..

Slightly tricky I can see, as date1 could be anything depending on the
report input query..

Regards

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:41***********************@news-text.dial.pipex.com...
Hi

I have a report which lists staff hours for various dates. What could be a
way to assign to each entry the week of the month that the date for the
entry belongs to?

Thanks

Regards

Nov 13 '05 #2
John wrote:
Week of month defined as;

if date1 is date from the first entry whatever it is then
date1 - date1+6 = week 1
date1+7 - date1+13 = week 2

and so on..

Slightly tricky I can see, as date1 could be anything depending on the
report input query..

Regards

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:41***********************@news-text.dial.pipex.com...
Hi

I have a report which lists staff hours for various dates. What could be a
way to assign to each entry the week of the month that the date for the
entry belongs to?

Thanks

Regards

Here I use 10/1/2004 as the start date. I subtract the days between
start/end, divide by 7 to get the week, and add 1 to get the week so
that the number of the week starts at 1, not 0.

'week where date is same as start day
? datediff("d",#10/1/2004#,#10/1/2004#+7)\7 + 1
1
'week 7 days out
? datediff("d",#10/1/2004#,#10/1/2004#+7)\7 + 1
2

Nov 13 '05 #3
"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:41***********************@news-text.dial.pipex.com...
Hi

I have a report which lists staff hours for various dates. What could be a
way to assign to each entry the week of the month that the date for the
entry belongs to?

Thanks

Regards

Create a table to hold all your distinct time periods - in this case weeks.
Label each row with the correct week number and join to the new table in