Macro that counts weeks from start date to end date

I'd appreciate your help.

I have macro that counts weeks in between two dates, as seen in file after pressing the button.

Please Login or Register  to view this content.

I have problem if 'end date' is in next year, therefore I would need a code that would recognize 'next year'.
Current year weeks are as from column BO to DN and next year should start with week 1 as from column DO.

Any suggestions?
Many thanks!

Thank you for effort.

As you can see in attached file the formula works perfect if my start date and end date are both in 2016. Column BO is week1, BP week2, BR week3, etc. Meaning if start date is 13.1.2016 (week2) and end date is 27.1.2016 (week4) it will start by imputing weeks as from column BP and placing into cell BP3 number 2, then number 3 into BQ3 and number 4 into BR3.

The last week of the present year is week 52 presented in column DN. Now if end date is in 2017 (meaning that counting exceeds 52) instead of starting it again with week1 in column BO, I'd like it to continue placing week1 (1) into column DO and continue. So basically I'll have weeks for two years in the same row.

Hope it's clearer.

I appreciate help.

Try this amendment to your code:

Please Login or Register  to view this content.

It looks at the date columns instead of the weeks columns.

Hi, the week is controlled by the Format function.

Below is the code you can tweak to your needs. Maybe you can try using type 3.

Please Login or Register  to view this content.

Constant	Value	Description
vbUseSystem	0	Use NLS API setting.
vbFirstJan1	1	Start with week in which January 1 occurs (default).
vbFirstFourDays	2	Start with the first week that has at least four days in the year.
vbFirstFullWeek	3	Start with the first full week of the year.

When do you start counting the week number?
1st Jan? Use type 1
First week that has at least 4 days in the year? Use type 2
First full week in the year? Use type 3

Normally, counting from 1st Jan usually gives a week 53. What would you do if you want only up to week 52?