Count Weeks in a Month

D

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!
 
D

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.
 
D

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


Dale Fye said:
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 said:
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!
 
D

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.



David127 said:
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


Dale Fye said:
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 said:
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!
 
D

David127

Jan 31st should be the last week of January.

Dale Fye said:
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.



David127 said:
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


Dale Fye said:
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!
 
D

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

David127 said:
Jan 31st should be the last week of January.

Dale Fye said:
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.



David127 said:
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!
 
J

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

Dale said:
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

David127 said:
Jan 31st should be the last week of January.

Dale Fye said:
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!
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"and" criteria 1
Grouping COLUMNS. 2
Spreadsheet Formulas 0
3 Weeks Forecast-Excel 2003 4
Weeks in a Calendar 2
lookup or match? 5
counting weeks in current month. 1
Doing Averages 1

Top