Date to week number using VBA

[Pinkster]

Hi all, I need the VBA code for the following!

I need to convert a DATE entered in lets say TextBox 1 and have the Week Number for that particular Date in TextBox 1 displayed in TextBox 2

Can anyone help me with this as it is rattling my brain!

Thanks in advance guys!

 

[tmcfadden]

There's a pretty extensive explanation of this here:

http://www.cpearson.com/excel/weeknum.htm

-Tim

 

[wigi]

Yes Tim.

Put in a new Module the following:

Sub textboxes()
With Sheets("Sheet1")
    .TextBox2 = WeekNum(.TextBox1)
End With
End Sub

Function WeekNum(D As Date) As Integer
     WeekNum = CInt(Format(D, "ww", 2))
End Function

Change the name of the textboxes and the sheet if needed.

Wigi

 

[Pinkster]

Thanks Guys, worked a treat!