Week of the month

[John]

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

[John]

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

[Salad]

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

[John Winterbottom]

"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
your queries.