Calc # Weeks Based on Date Cell (2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Does that do what you want?

Reply | Quote

well, duh!

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

Reply | Quote

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.

Reply | Quote

I’m sure you can figure out the rest!

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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(A1,0)-EOMONTH(A1,-1)=30,WEEKDAY(EOMONTH(A1,0))=1),6,IF(AND(EOMONTH(A1,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

Reply | Quote

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:

=WEEKNUM(EOMONTH(A1,0))-WEEKNUM(EOMONTH(A1,-1)+1)+1

Steve

Reply | Quote

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

Reply | Quote