# Thread: Calc # Weeks Based on Date Cell (2003)

1. ## Calc # Weeks Based on Date Cell (2003)

Is there a way to calculate the number of weeks in a month based on a date cell?

E.g. 8/31/05 = 5 weeks or 4 full weeks and 1 partial week

2. ## Re: Calc # Weeks Based on Date Cell (2003)

say your date is in cell A1
In an empty cell, type =DAY(A1)/7

Does that do what you want?

3. ## Re: Calc # Weeks Based on Date Cell (2003)

well, duh!

I was trying to make this so much more complicated that it was. I should have known that!

4. ## Re: Calc # Weeks Based on Date Cell (2003)

Just realized, though, that this really only works if the date in the cell is the last day of the month. Otherwise it only tells you how far into the month you are...Don't know if that will impact what you're doing or not.

5. ## Re: Calc # Weeks Based on Date Cell (2003)

Bans solution works provided the source cell is the last day in the month, which is easy to ceate. If that isn't always so,

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))/7

6. ## Re: Calc # Weeks Based on Date Cell (2003)

I'm sure you can figure out the rest! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

7. ## Re: Calc # Weeks Based on Date Cell (2003)

For what it is worth, a month can not have five weeks. 7 times 5 is 35, and no month has more than 31 days. A month always has four full weeks, and if it is not February in a non leap year, part of a week. A month can have five of whatever day the 1st, 2nd, or 3rd falls on. So, what is your definition of a month with five weeks?

8. ## Re: Calc # Weeks Based on Date Cell (2003)

I was trying to count the fact that a month could cross a 5th week, like the month of September.

9. ## Re: Calc # Weeks Based on Date Cell (2003)

All months, except for February in non-leap years do that.

10. ## Re: Calc # Weeks Based on Date Cell (2003)

If you are counting partial weeks plus full weeks, then
Nov 97 has 2 partial weeks + 4 full weeks = 6
Dec 97 has 2 partial weeks + 3 full weeks = 5
Jan 98 has 1 partial week + 4 full weeks = 5
Feb 98 has just 4 full weeks = 4
Mar 98 has 1 partial week + 4 full weeks = 5
Apr 98 has 2 partial weeks + 3 full weeks = 5
May 98 has 2 partial weeks + 4 full weeks = 6

Assuming the date in A1, a formula for this would be:
=IF(AND(EOMONTH(A1,0)-EOMONTH(A1,-1)=28,WEEKDAY(EOMONTH(A1,0))=7),4,IF(AND(EOMONTH(A 1,0)-EOMONTH(A1,-1)=30,WEEKDAY(EOMONTH(A1,0))=1),6,IF(AND(EOMONTH(A 1,0)-EOMONTH(A1,-1)=31,WEEKDAY(EOMONTH(A1,0))<3),6,5)))

which reads if there are 28 days in the month and it begins on Monday, then there are 4 weeks; otherwise
if there are 30 days in the month and it begins on Saturday, then there are 6 weeks; otherwise
if there are 31 days in the month and it begins on Friday or Saturday, then there are 6 weeks; otherwise
there are 5 weeks!
That wins an ugly formula prize! --Sam

11. ## Re: Calc # Weeks Based on Date Cell (2003)

Since to use EOMONTH requires the Analysis toolpack, why not use WEEKNUM as well. I think you will get the same answer with the "prettier" formula:
<pre>=WEEKNUM(EOMONTH(A1,0))-WEEKNUM(EOMONTH(A1,-1)+1)+1</pre>

Steve

12. ## Re: Calc # Weeks Based on Date Cell (2003)

Much, much, much nicer. For some reason I saw EOMONTH but not WEEKNUM.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•