Change In Date Format to weeknumber automatically

  • Thread starter Thread starter Rajesh Sharma
  • Start date Start date
R

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
 
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.
 
Back
Top