Count Weeks in a Month

[David127]

I need to group dates into Week of the Month meaning Jan would have a Wk1,
Wk2...Wk4 and Feb would have Wk1....Wk4. How do I convert a specific date to
show what week number in the month it is? Addionally, each week starts Sunday
- Saturday so the first & last week of month may have fewer than 7 days.

Date Week#
10/1/08 - Wk1
10/ 15/08 - Wk3
10/31/08 - Wk

Thanks!

 

[Dale Fye]

You can use something like the following to determine the start date of the
week for a given date:

dateadd("d", 1-weekday([DateField]), [DateField])

but this only tells you the start date of the week. You could then use
something like the following to determine the week of the month that date
falls in:

?Day(dateadd("d", 1-weekday([DateField]), [DateField]))\7 + 1

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[David127]

Hi- This almost get's me there. The first few days of the month end up
becoming part of the previous month.

contactDate Wk of Wk
01-Feb-08 27-Jan-08 4
02-Feb-08 27-Jan-08 4
03-Feb-08 03-Feb-08 1
09-Feb-08 03-Feb-08 1
10-Feb-08 10-Feb-08 2
17-Feb-08 17-Feb-08 3
29-Feb-08 24-Feb-08 4
01-Mar-08 24-Feb-08 4
02-Mar-08 02-Mar-08 1

You can use something like the following to determine the start date of the
week for a given date:

dateadd("d", 1-weekday([DateField]), [DateField])

but this only tells you the start date of the week. You could then use
something like the following to determine the week of the month that date
falls in:

?Day(dateadd("d", 1-weekday([DateField]), [DateField]))\7 + 1

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I need to group dates into Week of the Month meaning Jan would have a Wk1,
Wk2...Wk4 and Feb would have Wk1....Wk4. How do I convert a specific date to
show what week number in the month it is? Addionally, each week starts Sunday
- Saturday so the first & last week of month may have fewer than 7 days.

Date Week#
10/1/08 - Wk1
10/ 15/08 - Wk3
10/31/08 - Wk

Thanks!

 

[Dale Fye]

so, what you want then is to identify the week based on the month at the end
of the week, is that correct? So if 1 Feb falls on a Wed, you want to call
that week 1 of Feb. In that same week, you also have 31 January, how do you
want to refer to that week (as the first week of Feb, or the last week of
January)?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hi- This almost get's me there. The first few days of the month end up
becoming part of the previous month.

contactDate Wk of Wk
01-Feb-08 27-Jan-08 4
02-Feb-08 27-Jan-08 4
03-Feb-08 03-Feb-08 1
09-Feb-08 03-Feb-08 1
10-Feb-08 10-Feb-08 2
17-Feb-08 17-Feb-08 3
29-Feb-08 24-Feb-08 4
01-Mar-08 24-Feb-08 4
02-Mar-08 02-Mar-08 1

You can use something like the following to determine the start date of the
week for a given date:

dateadd("d", 1-weekday([DateField]), [DateField])

but this only tells you the start date of the week. You could then use
something like the following to determine the week of the month that date
falls in:

?Day(dateadd("d", 1-weekday([DateField]), [DateField]))\7 + 1

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I need to group dates into Week of the Month meaning Jan would have a Wk1,
Wk2...Wk4 and Feb would have Wk1....Wk4. How do I convert a specific date to
show what week number in the month it is? Addionally, each week starts Sunday
- Saturday so the first & last week of month may have fewer than 7 days.

Date Week#
10/1/08 - Wk1
10/ 15/08 - Wk3
10/31/08 - Wk

Thanks!

 

[David127]

Jan 31st should be the last week of January.

so, what you want then is to identify the week based on the month at the end
of the week, is that correct? So if 1 Feb falls on a Wed, you want to call
that week 1 of Feb. In that same week, you also have 31 January, how do you
want to refer to that week (as the first week of Feb, or the last week of
January)?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hi- This almost get's me there. The first few days of the month end up
becoming part of the previous month.

contactDate Wk of Wk
01-Feb-08 27-Jan-08 4
02-Feb-08 27-Jan-08 4
03-Feb-08 03-Feb-08 1
09-Feb-08 03-Feb-08 1
10-Feb-08 10-Feb-08 2
17-Feb-08 17-Feb-08 3
29-Feb-08 24-Feb-08 4
01-Mar-08 24-Feb-08 4
02-Mar-08 02-Mar-08 1

You can use something like the following to determine the start date of the
week for a given date:

dateadd("d", 1-weekday([DateField]), [DateField])

but this only tells you the start date of the week. You could then use
something like the following to determine the week of the month that date
falls in:

?Day(dateadd("d", 1-weekday([DateField]), [DateField]))\7 + 1

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I need to group dates into Week of the Month meaning Jan would have a Wk1,
Wk2...Wk4 and Feb would have Wk1....Wk4. How do I convert a specific date to
show what week number in the month it is? Addionally, each week starts Sunday
- Saturday so the first & last week of month may have fewer than 7 days.

Date Week#
10/1/08 - Wk1
10/ 15/08 - Wk3
10/31/08 - Wk

Thanks!

 

[Dale Fye]

How about:

(day(Somedate) + 7 - weekday(Somedate))\7 + 1

Instead of computing the date of the saturday at the end of the week, this
computes a number (that could be greater than the number of days in the
month).

HTH
Dale

Jan 31st should be the last week of January.

so, what you want then is to identify the week based on the month at the
end
of the week, is that correct? So if 1 Feb falls on a Wed, you want to
call
that week 1 of Feb. In that same week, you also have 31 January, how do
you
want to refer to that week (as the first week of Feb, or the last week of
January)?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hi- This almost get's me there. The first few days of the month end up
becoming part of the previous month.

contactDate Wk of Wk
01-Feb-08 27-Jan-08 4
02-Feb-08 27-Jan-08 4
03-Feb-08 03-Feb-08 1
09-Feb-08 03-Feb-08 1
10-Feb-08 10-Feb-08 2
17-Feb-08 17-Feb-08 3
29-Feb-08 24-Feb-08 4
01-Mar-08 24-Feb-08 4
02-Mar-08 02-Mar-08 1


:

You can use something like the following to determine the start date
of the
week for a given date:

dateadd("d", 1-weekday([DateField]), [DateField])

but this only tells you the start date of the week. You could then
use
something like the following to determine the week of the month that
date
falls in:

?Day(dateadd("d", 1-weekday([DateField]), [DateField]))\7 + 1

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I need to group dates into Week of the Month meaning Jan would have
a Wk1,
Wk2...Wk4 and Feb would have Wk1....Wk4. How do I convert a
specific date to
show what week number in the month it is? Addionally, each week
starts Sunday
- Saturday so the first & last week of month may have fewer than 7
days.

Date Week#
10/1/08 - Wk1
10/ 15/08 - Wk3
10/31/08 - Wk

Thanks!

 

[John Spencer]

Nice solution. I've been pondering over this off and on for the last 16 hours.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

How about:

(day(Somedate) + 7 - weekday(Somedate))\7 + 1

Instead of computing the date of the saturday at the end of the week, this
computes a number (that could be greater than the number of days in the
month).

HTH
Dale

Jan 31st should be the last week of January.

so, what you want then is to identify the week based on the month at the
end
of the week, is that correct? So if 1 Feb falls on a Wed, you want to
call
that week 1 of Feb. In that same week, you also have 31 January, how do
you
want to refer to that week (as the first week of Feb, or the last week of
January)?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Hi- This almost get's me there. The first few days of the month end up
becoming part of the previous month.

contactDate Wk of Wk
01-Feb-08 27-Jan-08 4
02-Feb-08 27-Jan-08 4
03-Feb-08 03-Feb-08 1
09-Feb-08 03-Feb-08 1
10-Feb-08 10-Feb-08 2
17-Feb-08 17-Feb-08 3
29-Feb-08 24-Feb-08 4
01-Mar-08 24-Feb-08 4
02-Mar-08 02-Mar-08 1


:

You can use something like the following to determine the start date
of the
week for a given date:

dateadd("d", 1-weekday([DateField]), [DateField])

but this only tells you the start date of the week. You could then
use
something like the following to determine the week of the month that
date
falls in:

?Day(dateadd("d", 1-weekday([DateField]), [DateField]))\7 + 1

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I need to group dates into Week of the Month meaning Jan would have
a Wk1,
Wk2...Wk4 and Feb would have Wk1....Wk4. How do I convert a
specific date to
show what week number in the month it is? Addionally, each week
starts Sunday
- Saturday so the first & last week of month may have fewer than 7
days.

Date Week#
10/1/08 - Wk1
10/ 15/08 - Wk3
10/31/08 - Wk

Thanks!

 

[Tamara Bookal]

Very good approach but their is a slight error. When you try to use the formulae to evaluate June 2008 the results are incorrect. Eg June 2 day = 2,weekday = 2 putting these values into formulae
(day(Somedate) + 7 - weekday(Somedate))\7 + 1
=(2+7-2)\7 + 1
=7\7 + 1
=1 +1
=2 (Which means that the week number would start at two. This will happen everytime the of day and weekday is the same).
To correct for this anomoly simply use:
(day(Somedate) + 6 - weekday(Somedate))\7 + 1

TYB

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng