No of weeks (Wednesdays)in month

**GerryA** · 06-13-2012, 08:42 AM

Hi,

The following formula:

=4+(DAY(B4-DAY(B4)+35)<WEEKDAY(B4-DAY(B4)+4))

correctly calculates the number of Wednesdays in a month but I am having some difficulty in getting this to work in VBA. When I put this into VBA the results are not the same so obviously I cannot just paste this formula in as code. I am still learning Excel vba so hope that this will be an easy one for someone more experienced than me.

This is my code so far:
--------------------------------------------------------------

Please Login or Register  to view this content.

---------------------------------------------------------------------------------

In essence I'm trying to create a spreadsheet for each of our projects which has a start date and and estimated completion date. I will then loop through each of the months for the duration of the project, calculate the no. of weeks in each month and then set up each week as a column in the spreadsheet. However I am stuck in the early stages of the process.

Thanking you in advance for any assistance/advice.

Gerry

**GerryA** · 06-13-2012, 08:48 AM

Sorry Jeff. Thanks for pointing it out.

**WasWodge** · 06-13-2012, 10:08 AM

Can I just ask in what way do you mean the results are different?

When I put this into VBA the results are not the same so obviously I cannot just paste this formula in as code

**GerryA** · 06-13-2012, 04:49 PM

Originally Posted by WasWodge

Can I just ask in what way do you mean the results are different?

Hi, If I run the formula in the spreadsheet I get:

04/07/12 4
04/08/12 5
04/09/12 4
04/10/12 5
04/11/12 4
04/12/12 4

but when I run the code in the immediate window I get

04/07/12 4
04/08/12 3
04/09/12 4
04/10/12 4
04/11/12 3
04/12/12 4

Thanks for looking.

**WasWodge** · 06-13-2012, 05:29 PM

I have just inserted the formula manually in Column D and used the macro below to fill column E (see the table formula at the bottom of this post) and the results were the same.

Please Login or Register  to view this content.

Cell Formula
D3 =4+(DAY(B4-DAY(B4)+35)<WEEKDAY(B4-DAY(B4)+4))
E3 =4+(DAY(B4-DAY(B4)+35)<WEEKDAY(B4-DAY(B4)+4))
D4 =4+(DAY(B5-DAY(B5)+35)<WEEKDAY(B5-DAY(B5)+4))
E4 =4+(DAY(B5-DAY(B5)+35)<WEEKDAY(B5-DAY(B5)+4))
D5 =4+(DAY(B6-DAY(B6)+35)<WEEKDAY(B6-DAY(B6)+4))
E5 =4+(DAY(B6-DAY(B6)+35)<WEEKDAY(B6-DAY(B6)+4))
D6 =4+(DAY(B7-DAY(B7)+35)<WEEKDAY(B7-DAY(B7)+4))
E6 =4+(DAY(B7-DAY(B7)+35)<WEEKDAY(B7-DAY(B7)+4))
D7 =4+(DAY(B8-DAY(B8)+35)<WEEKDAY(B8-DAY(B8)+4))
E7 =4+(DAY(B8-DAY(B8)+35)<WEEKDAY(B8-DAY(B8)+4))
D8 =4+(DAY(B9-DAY(B9)+35)<WEEKDAY(B9-DAY(B9)+4))
E8 =4+(DAY(B9-DAY(B9)+35)<WEEKDAY(B9-DAY(B9)+4))
D9 =4+(DAY(B10-DAY(B10)+35)<WEEKDAY(B10-DAY(B10)+4))
E9 =4+(DAY(B10-DAY(B10)+35)<WEEKDAY(B10-DAY(B10)+4))
D10 =4+(DAY(B11-DAY(B11)+35)<WEEKDAY(B11-DAY(B11)+4))
E10 =4+(DAY(B11-DAY(B11)+35)<WEEKDAY(B11-DAY(B11)+4))

**GerryA** · 06-13-2012, 10:28 PM

Hi, I'm still coming to grips with VGA. With regard to the test you did, does your code just auto fill column E with the formula? Should the formula work in VBA if pasted in 'as is'? It's just that it doesn't calculate the same number of weeks for me as running the formula in the spreadsheet itself. Many thanks for your help by the way,

**jeffreybrown** · 06-13-2012, 10:44 PM

Hi GerryA,

Sorry coming in a litte late, but this sounds confusing. Your thread title has both no of weeks and Wednesdays so which is it?

Also, you say you have a start date and competion date, for no of weeks, why not, (enddate-startdate) / 7

I don't see the need for VBA and in post #4 not quite understanding all the dates.

Can you maybe attach a sample workbook if that will help to explain it better? What you have and what you desire?

**GerryA** · 06-14-2012, 02:51 AM

Hi Jeff, Yes 'confusing' is my middle name

Our pay week begins on a Wednesday so I'm trying to determine the no. of Wednesdays in each month. I just realized that my dates may look like they are in mm/dd/yy format but they are in dd/mm/yy format so in other words 4th July, 4th August etc. so the first grouping of dates in post 4 is where I've just got the formula in the sheet and calculated the number of Wednesdays in each month so the result was 4 Wednesdays, 5 Wednesdays etc.

The second grouping of dates is where I've got the formula in vb code and run it in the immediate window and the number of Wednesdays arrived at is different to the result I obtained when I just ran the formula in the worksheet. I thought I'd use VB code as there was something else that I wanted to do once I had obtained the correct result. Perhaps I was being a bit ambitious and should look at another way to build the spreadsheet. I'm on my iPad at the moment so when I get back to my laptop I'll put up a sample of what I'm trying to achieve. Thanks for your input Jeff.