By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

Week of the month

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
"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.
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.