Excel VBA Format function does not return desired date

I have an Excel sheet with E2 = "10-Apr-16" as Custom format. And I would like to return its week number (maybe 16). The code I used (with E2 active):

With ActiveCell
    .NumberFormat = "mm/dd/yyyy"
    .Value = Date
End With
oneDate = Format(Range("E2").Select, "mm/dd/yyyy")
weekNumber = DatePart("ww", oneDate)

oneDate turned out to be 12/29/1899, and WeekNumber to be 52. What is wrong with my code???

Update:

I later changed code to:

Range("E:E").NumberFormat = "m/dd/yyyy"
oneDate = Range("E2")
oneDate = Format(Range("E2").Select, "m/dd/yyyy")
weekNumber = DatePart("ww", oneDate)

With this, I get 12/29/1899 as oneDate, and 52 as weekNumber.

Without the third line (or even without first line), the code will work properly and return 16 as weekNumber;

With the third line (no matter with/without first line), it still returned 12/29/1899.

I think the problem is with the code in third line, but not sure what it is. Does anybody know how I did it wrong?

Solution

Your code inserts the system date into ActiveCell (whichever one that happens to be), then you set oneDate to E2

I don't know entirely what you're hoping to achieve, but it seems that you can achieve what you've stated via

weekNumber = datepart("ww", Range("E2"))

As long as what's in E2 is actually a date serial number which Excel recognises as a date, and not simply text which looks like a date (an important distinction)

If weekNumber is still wrong, try

weekNumber = datepart("ww",date)

This will get the current week number, and means that E2 is not really a date as far as Excel is concerned