# Getting Week As 1st week , 2nd week 3rd week or 4th Week of a particular Date in a month

#### SamDsouza

##### Board Regular
Hello

How can i get the week defined as 1st week ,2nd week , 3rd week and 4th week of a particular date which falls in Particular Month
For eg if i enter 01-Feb-2019 ie in dd-mmm-yyyy then in next adjacent cell to display as 1st Week of Feb 19
14-Jan-2019 to be dispalyed as 3rd Week of Jan 19
Require VBA code

thanks
SamD

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
does it have to be VBA?
this formula should work with date in A1
=WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1

Thanks fhqwgads for Sheet formula in cell A1

Yes VBA code as wanted to add "Week" after the value derived in Cell A1

SamD

=IF(WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1=1,"1st",IF(WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1=2,"2nd",IF(WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1=3,"3rd",IF(WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1=4,"4th","5th"))))&" Week of " & TEXT((A1),"mmm yy")

sorry for formula, i'm not great with VBA

Try this:-
Dates in columns "A", Month week count in column "B".
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG06Feb32
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, fdate [COLOR="Navy"]As[/COLOR] Date, DDif [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] IsDate(Dn.Value) [COLOR="Navy"]Then[/COLOR]
fdate = DateSerial(Year(Dn.Value), Month(Dn.Value), 1)
DDif = DateDiff("d", fdate, Dn.Value)
[COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] DDif
[COLOR="Navy"]Case[/COLOR] 0 To 6: Num = 1
[COLOR="Navy"]Case[/COLOR] 7 To 13: Num = 2
[COLOR="Navy"]Case[/COLOR] 14 To 20: Num = 3
[COLOR="Navy"]Case[/COLOR] 21 To 27: Num = 4
[COLOR="Navy"]Case[/COLOR] 27 To 31: Num = 5
[COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]End[/COLOR] If
Dn.Offset(, 1) = Num
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

Thankx Fhqwgads for your cell formula and will explore the same thru VBA.
Thankx MickG for your coding have not tried. Will try and let you know

Here is my take on it:

a more compact formula

=MID("1st2nd3rd4th5th",(WEEKNUM(A1,2)-WEEKNUM(EOMONTH(A1,-1)+1,2))*3+1,3)&TEXT(A1," W\e\ek of mmm yy")

and a VBA code based on it
Code:
``````Sub xWeek()
Dim Rng As Range
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
With Rng
.Offset(, 1) = Evaluate("IF({1},MID(""1st2nd3rd4th5th"",(WEEKNUM(+" & .Address & ",2)-WEEKNUM(EOMONTH(+" & .Address & ",-1)+1,2))*3+1,3)&TEXT(" & .Address & ","" W\e\ek of mmm yy""))")
End With
End Sub``````

Thankx Tetra201

Something New will try yours also and let you know

Tetra201
=MID("1st2nd3rd4th5th",(WEEKNUM(A1,2)-WEEKNUM(EOMONTH(A1,-1)+1,2))*3+1,3)&TEXT(A1," W\e\ek of mmm yy")
and Really thankx for VBA code too.

=IF(WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1=1,"1st",IF(WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1=2,"2nd",IF(WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1=3,"3rd",IF(WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1=4,"4th","5th"))))&" Week of " & TEXT((A1),"mmm yy")

Thanks Fhqwgads and Tetra201 yours were mind blowing one and quite amazed to see the same result in many ways

MickG tried yours too but result was far apart Thanks any ways.
SamD

Replies
9
Views
642
Replies
1
Views
364
Replies
3
Views
166
Replies
4
Views
540
Replies
1
Views
210

1,202,987
Messages
6,052,938
Members
444,616
Latest member
novit19089

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back