Change date to display as # week of the year

[Peruanos72]

Hello all,

I have the following code and I want to change the format from "mmmm" to "ww"
so the cell will display the week of the year. i.e. week 13, week 12 but
only the number is needed. i.e. "13".

i've tried change the "mmmm" to "ww" but it displays "13" for every date. I
have dates from last week and the date for today so i expected to see 12 for
the dates from last week and 13 for todays date.

Any thoughts? Thanks in advance!!

Here's my code....

With Columns(2)

Set b = .Find(what:="", after:=Cells(1, 2))

b.Select
ActiveSheet.Paste
b.Offset(0, -1).Select
Selection = b

End With

Dim sStr As String
sStr = Format(Date, "mmmm")

With Columns(1)

Set a = .Find(what:="", after:=Cells(1, 1))

a.Offset(-1, 0).Select
Selection = sStr

End With

 

[Jeff]

Try Here...
http://www.cpearson.com/Excel/weeknum.htm

 

[JLGWhiz]

I am not sure where your other dates are but this code:

sStr = Format(Date, "mmmm")

evaluates today's date. If you are trying to locate a date on the worksheet
with your Find statement, then you would want to use the results of that in
the Format function to get the week. example:

Set dt = ActiveSheet.Cells.Find("x", LookIn:=xlValues)
If Not dt Is Nothing Then
myDate = dt.Offset(0, -1)
sStr = Format(myDate, "ww")
End If

This psuedo code assumes that a valid date is entered one cell to the left
of the cell containing "x". If the "x" is found, then a variable is
assigned the value of the cell to the left of the found cell. That variable
is then used to assign the week number to a new variable (sStr) using the
Format function.

 

[Peruanos72]

Thanks Jeff. I had a feeling it was working correctly and that it was the
year starting on a Thursday that was throwing it off. I'm looking to have the
cell show each week as Monday thru Friday.

i'm fairly new to programming and the website you gave me has the following
code

Function VBAWeekNum(D As Date, FW As Integer) As Integer
VBAWeekNum = CInt(Format(D, "ww", FW))
End Function

Where can I put this in my code so that it formats the cell properly? The
website also said I can set it to "2" so the week begins on Monday but I
don't know where to put this variable within the code either.

Thanks again for any help you can throw my way...