Count dates to show week number sequence

[rashid67]

Hello,

I am looking for a formula to show "Wk 1" for seven consecutive dates and than show "Wk 2" and so on till the end of the year date. The example is shown below. In this case the fiscal year starts from Feb 01 of current year and end Jan 31 of the following year, this is a retail calendar.

I would appreciate help from any one....

Thanks,
RJ

Date		Week
02:01:2017		Wk 1
02:02:2017		Wk 1
02:03:2017		Wk 1
02:04:2017		Wk 1
02:05:2017		Wk 1
02:06:2017		Wk 1
02:07:2017		Wk 1
02:08:2017		Wk 2
02:09:2017		Wk 2
02:10:2017		Wk 2
02:11:2017		Wk 2
02:12:2017		Wk 2
02:13:2017		Wk 2
02:14:2017		Wk 2
02:15:2017		Wk 3

<tbody>
</tbody>

 

[oldbrewer]

01-Feb-17

Wk 1

02-Feb-17

Wk 1

03-Feb-17

Wk 1

04-Feb-17

Wk 1

05-Feb-17

Wk 1

06-Feb-17

Wk 1

07-Feb-17

Wk 1

08-Feb-17

Wk 2

09-Feb-17

Wk 2

this macro put in the week numbers

10-Feb-17

Wk 2

11-Feb-17

Wk 2

I only scaled it to 6 weeks for demo purposes

12-Feb-17

Wk 2

13-Feb-17

Wk 2

no doubt you have special rules for extra days in week at year ends

14-Feb-17

Wk 2

15-Feb-17

Wk 3

tot = 1

16-Feb-17

Wk 3

For j = 1 To 42

17-Feb-17

Wk 3

Sum = Sum + 1

18-Feb-17

Wk 3

Cells(j, 2) = "Wk " & tot

19-Feb-17

Wk 3

If Sum = 7 Then Sum = 0: tot = tot + 1

20-Feb-17

Wk 3

Next j

21-Feb-17

Wk 3

End Sub

22-Feb-17

Wk 4

23-Feb-17

Wk 4

24-Feb-17

Wk 4

25-Feb-17

Wk 4

26-Feb-17

Wk 4

27-Feb-17

Wk 4

28-Feb-17

Wk 4

01-Mar-17

Wk 5

02-Mar-17

Wk 5

03-Mar-17

Wk 5

04-Mar-17

Wk 5

05-Mar-17

Wk 5

06-Mar-17

Wk 5

07-Mar-17

Wk 5

08-Mar-17

Wk 6

09-Mar-17

Wk 6

10-Mar-17

Wk 6

11-Mar-17

Wk 6

12-Mar-17

Wk 6

13-Mar-17

Wk 6

14-Mar-17

Wk 6

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

 

[Joe4]

That is an odd looking date format. Are your dates entered as dates, and just formatted with a special custom format, or are they entered as strings?
And what row is the first date (February 2) appearing on?

 

[rashid67]

That is an odd looking date format. Are your dates entered as dates, and just formatted with a special custom format, or are they entered as strings?
And what row is the first date (February 2) appearing on?

dates are in date format: mm:dd:yyyy
February 2 is on row 3 (A3)

 

[Joe4]

Enter this formula in B3 and copy down.

="Wk " & INT((A3-$A$3)/7)+1

 

[rashid67]

Thanks.

 

[Joe4]

You are welcome!