count 4 & 5 week months

[cybilistic]

Hi Excel gurus!

I'm hoping someone can help me figure this out. I have a spreadsheet that I am using for capacity planning. We want to automatically figure out which months have 4 weeks and which months have 5 weeks.

The weeks in a month are defined by work week (Mon to Fri), and if the work week has 3 or more days, it is considered a week of that month, if it has two or less days, it is not counted as a week during that month (it will be counted part of the following month).

ie: April 2014 would be a 5 week month, May 2014 would be a 4 week month and June would be a 4 week month.

Hope that makes sense. I have also posted part of my spreadsheet below for further detail.

Thanks!

	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Nov-14	Dec-14	Jan-15	Feb-15	Mar-15
	5	5	5	5	5	4	4	4	4	4	4	4	4	5	5	5	5	5	4	4	4	4	4	4	4	4	5	5	5	5	5	4	4	4	4	5	5	5	5	5	4	4	4	4	4	4	4	4	4	4	4	4
	W1	W2	W3	W4	W5	W1	W2	W3	W4	W1	W2	W3	W4	W1	W2	W3	W4	W5	W1	W2	W3	W4	W1	W2	W3	W4	W1	W2	W3	W4	W5	W1	W2	W3	W4	W1	W2	W3	W4	W5	W1	W2	W3	W4	W1	W2	W3	W4	W1	W2	W3	W4

<tbody>
</tbody>

 

[AlphaFrog]

This counts the Wednesdays in a month for a given date (A1). It returns a 4 or 5.

=4+(DAY(A1-DAY(A1)+35)< WEEKDAY(A1-DAY(A1)-3))<weekday(a1-day(a1)-3))< html=""></weekday(a1-day(a1)-3))<>

 

[markmzz]

Maybe this:

=4+(WEEKDAY(A1,2)<4)*(WEEKDAY(EOMONTH(A1),2)>2)

Markmzz

 

[cybilistic]

This counts the Wednesdays in a month for a given date (A1). It returns a 4 or 5.

=4+(DAY(A1-DAY(A1)+35)< WEEKDAY(A1-DAY(A1)-3))<weekday(a1-day(a1)-3))< html=""></weekday(a1-day(a1)-3))<>

Thanks AlphaFrog! I think this is a great way to count this - by counting the Wednesdays in the month! What do you have in cell A1? I've tried it on my spreadsheet and A1=April 1, 2014, then I do month by month by using the 1st of each month... however all I got returned was 4's, no 5's. Might be that I am doing something wrong.

Thanks!

 

[cybilistic]

Maybe this:

=4+(WEEKDAY(A1,2)<4)*(WEEKDAY(EOMONTH(A1),2)>2)

Markmzz

Thanks Markmzz! I entered this onto my spreadsheet and used A1 = April 1, 2014. It all seemed good and was giving me 4 and 5 weeks for the appropriate months until I got to September 1, 2014. Any ideas? Maybe I'm using incorrect data for A1?

Thanks for your help!

 

[markmzz]

Thanks Markmzz! I entered this onto my spreadsheet and used A1 = April 1, 2014. It all seemed good and was giving me 4 and 5 weeks for the appropriate months until I got to September 1, 2014. Any ideas? Maybe I'm using incorrect data for A1?

Thanks for your help!

Here the formula (changed for your data of the post #1) works. Look at this:

Layout

abr-14

mai-14

jun-14

jul-14

ago-14

set-14

out-14

nov-14

dez-14

jan-15

fev-15

mar-15

5

5

5

5

5

4

4

4

4

4

4

4

4

5

5

5

5

5

4

4

4

4

4

4

4

4

5

5

5

5

5

4

4

4

4

5

5

5

5

5

4

4

4

4

4

4

4

4

4

4

4

4

W1

W2

W3

W4

W5

W1

W2

W3

W4

W1

W2

W3

W4

W1

W2

W3

W4

W5

W1

W2

W3

W4

W1

W2

W3

W4

W1

W2

W3

W4

W5

W1

W2

W3

W4

W1

W2

W3

W4

W5

W1

W2

W3

W4

W1

W2

W3

W4

W1

W2

W3

W4

Share

Share this post on

Digg

Del.icio.us

Technorati

Twitter

Reply With QuoteReply With Quote

********************************

******

***

***

***

***

******

***

***

***

******

***

***

***

******

***

***

***

***

******

***

***

***

******

***

***

***

******

***

***

***

***

******

***

***

***

******

***

***

***

***

******

***

***

***

******

***

***

***

******

***

***

***

<tbody>
</tbody>

Formula:

B2-> =4+(WEEKDAY(MAX($B1:B1),2) < 4)*(WEEKDAY(EOMONTH(MAX($B1:B1),0),2) > 2)

What you got to September 1, 2014?


Markmzz

 

[cybilistic]

Here the formula (changed for your data of the post #1) works. Look at this:

Layout (REMOVED)

Formula:

B2-> =4+(WEEKDAY(MAX($B1:B1),2) < 4)*(WEEKDAY(EOMONTH(MAX($B1:B1),0),2) > 2)

What you got to September 1, 2014?


Markmzz

Thanks Markmzz!! I copied your new code and it seemed to work this time - I also tested it on 2016 dates and it also seemed to return the correct 4 and 5 week months.

So happy! Thank you for sharing your brilliance!

To answer your question, September was getting a 5 week month returned, when it's only a 4 week month.. .but all good now!

Cheers!

 

[markmzz]

Thanks Markmzz!! I copied your new code and it seemed to work this time - I also tested it on 2016 dates and it also seemed to return the correct 4 and 5 week months.

So happy! Thank you for sharing your brilliance!

To answer your question, September was getting a 5 week month returned, when it's only a 4 week month.. .but all good now!

Cheers!

You're welcome and thanks for the feedback.

If necessary, here you have the formulas to create your layout of post #1:

In B1 type Apr-14 and in C1 type this formula:

C1-> =IF(COLUMNS($B1:C1)-MATCH(8^7,$B1:B1)=(4+(WEEKDAY(MAX($B1:B1),2) < 4)*(WEEKDAY(EOMONTH(MAX($B1:B1),0),2) > 2)),EDATE(MAX($B1:B1),1),"")

Or this

C1-> =IF(COLUMNS($B1:C1)-MATCH(8^7,$B1:B1)=B$2,EDATE(MAX($B1:B1),1),"")

And copy to the right.

In B2 type this formula:

B2-> =4+(WEEKDAY(MAX($B1:B1),2) < 4)*(WEEKDAY(EOMONTH(MAX($B1:B1),0),2) > 2)

And copy to the right.

In B3 type this formula:

B3-> ="W"&IF(B1<>"",1,RIGHT(A8,1)+1)

And copy to the right.

Markmzz

 

[cybilistic]

You're welcome and thanks for the feedback.

If necessary, here you have the formulas to create your layout of post #1:

In B1 type Apr-14 and in C1 type this formula:

C1-> =IF(COLUMNS($B1:C1)-MATCH(8^7,$B1:B1)=(4+(WEEKDAY(MAX($B1:B1),2) < 4)*(WEEKDAY(EOMONTH(MAX($B1:B1),0),2) > 2)),EDATE(MAX($B1:B1),1),"")

Or this

C1-> =IF(COLUMNS($B1:C1)-MATCH(8^7,$B1:B1)=B$2,EDATE(MAX($B1:B1),1),"")

And copy to the right.

In B2 type this formula:

B2-> =4+(WEEKDAY(MAX($B1:B1),2) < 4)*(WEEKDAY(EOMONTH(MAX($B1:B1),0),2) > 2)

And copy to the right.

In B3 type this formula:

B3-> ="W"&IF(B1<>"",1,RIGHT(A8,1)+1)

And copy to the right.

Markmzz

Thanks Markmzz! This is useful. For the last formula (the one I type in B3: "B3-> ="W"&IF(B1<>"",1,RIGHT(A8,1)+1)" What is, or should be in cell A8? I did the copy & pasting as shown & it gives me a #VALUE error on weeks W2, W3, W4 and when applicable, W5, shown below. Not sure why. W1 in every month seems to work. Thanks again!

	Apr-14
	5	5	5	5	5
	W1	#VALUE!	#VALUE!	#VALUE!	#VALUE!

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

 

[markmzz]

Thanks Markmzz! This is useful. For the last formula (the one I type in B3: "B3-> ="W"&IF(B1<>"",1,RIGHT(A8,1)+1)" What is, or should be in cell A8? I did the copy & pasting as shown & it gives me a #VALUE error on weeks W2, W3, W4 and when applicable, W5, shown below. Not sure why. W1 in every month seems to work. Thanks again!

	Apr-14
	5	5	5	5	5
	W1	#VALUE!	#VALUE!	#VALUE!	#VALUE!

<tbody>
</tbody>

Hi Cybilistic,

I'm sorry, my mistake.

The correct formula is:

B3-> ="W"&IF(B$1<>"",1,RIGHT([COLOR="#FF0000"][B]A$3[/B][/COLOR],1)+1)

Markmzz