Change In Date Format to weeknumber automatically

[Rajesh Sharma]

Hi,
I need to change the entire column of dates in Weeknumber format
for example: column named CRD,CAD,AA and AB is in the date format
10/30/03, this must be changed as 44.
Have followed these steps & got the results
----------------------------------------------------------1. Go to tools
- > Add-Ins - >
2. Chck/Select the Analysis - ToolPak Add-In
3. Once done the feature allows you to give the the week number
4. eg. Type today's date ie. 15/10/2003 in cell A1
5. In cell B1 type the formula =WEEKNUM(A1) and u get the weeknumber
display .. ie. 44
----------------------------------------------------------
it works but this must be done one by one, ie. cell by cell. it is
possible to change the entire column without adding a extra column?

What I require - Enter a normal date in the format dd/mm/yyyy and it
automatically changes into the respective week number
Please revert asap

Warm regards,
Rajesh

 

[Tom Ogilvy]

Sub ConvertToWeekNum()
Dim rng As Range
Set rng = Range(Cells(1, ActiveCell.Column), _
Cells(Rows.Count, ActiveCell.Column).End(xlUp))
Varr = rng.Value
For i = 1 To UBound(Varr, 1)
Varr(i, 1) = DatePart("ww", Varr(i, 1))
Next
rng.Value = Varr
End Sub

This is lightly tested, but it appears to give the same result as weeknum.

Note that this is not the ISO standard for the Week number.