Solved: Date formula in vba

[ukdane]

I found this formula elsewhere in the forum (thanks xld!)
=INT((A2- DATE(YEAR(A2),1,0)+6)/7)

where A2 is a cell containing todays date.

However, I want to use this formula in VBA to generate the same result, but attach it to a variable.
(and replace A2 with another variable which contains a date)

myvar = INT((mytoday - DATE(YEAR(mytoday),1,0)+6)/7)

but the whole DATE bit of the code doesn't work in VBA

How do I get this to work?

Cheers

[Bob Phillips]

[vba]

myvar = Int((mytoday - DateSerial(Year(mytoday), 1, 0) + 6) / 7)
[/vba]

[ukdane]

As always

[ukdane]

Hi again,
I was just testing this code again, and I've discovered an error.

If we look at the dates for 2010, january 1st, 2nd and 3rd all should fall in week 53 (but using this code, are in week 1.
January 8,9, and 10 2010 should be in week 1, but fall in week 2, and so on, and so forth.
Basically, the code doesn't take into account that week 53 needs to be 7 days (this doesn not occur every year), and that week 1 needs to start accordingly.

The rules are such that:
1) Week 1 always includes January 4th.
2) for non-leap years, Week 53 always includes January 3rd BUT week 53 only exists if 27 December is a Sunday.
3) for leap years, Week 53 always includes January 2nd BUT week 53 only exists if 26 December is a Saturday OR a Sunday

nice and simple

[Bob Phillips]

Sounds like you want ISO week numbers, not MS week numbers

=INT((A2-WEEKDAY(A2,2)-DATE(YEAR(A2+4-WEEKDAY(A2,2)),1,4))/7)+2