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

[SamDsouza]

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

 

[fhqwgads]

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

 

[SamDsouza]

Thanks fhqwgads for Sheet formula in cell A1

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

SamD

 

[fhqwgads]

how about this?
=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

 

[MickG]

Try this:-
Dates in columns "A", Month week count in column "B".

[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

 

[SamDsouza]

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

 

[Tetra201]

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

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

 

[SamDsouza]

Thankx Tetra201

Something New will try yours also and let you know

 

[SamDsouza]

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.

and Fhqwgads

=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