week number issue in macro

Hello all

I have this macro where it enter in a cell the week's number followed by the date.

The problem i have is that the week's number is not good.

If i take for example, today, we are on week 50.

But is i activate the macro, the result i have is: Semaine:52 Sunday Dec 18 2011

How i can fix this?

Thanks again

Private Sub Calendar1_Click()
Dim t As Long
  t = DateSerial(Year(D + (8 - Weekday(D)) Mod 7 - 3), 1, 1)
  NumSemaine = ((D - t - 3 + (Weekday(t) + 1) Mod 7)) \ 7 + 1

  Unload Me
ActiveCell.Value = "Semaine:" & NumSemaine & " " & Format(Calendar1.Value, "dddd mmm dd yyyy")
End Sub

Open in new window

LVL 11
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Looking at http://www.cpearson.com/excel/WeekNumbers.aspx, it depends on how you want to define your week number.

I believe you want the ISO week number (international standard).

So you should implement:

Public Function IsoWeekNumber(InDate As Date) As Long
    IsoWeekNumber = DatePart("ww", InDate, vbMonday, vbFirstFourDays)
End Function
You could, of course, also use one of the other formulas in the link - but this one should give you the most accurate week number.

Just add the function about your Calendar1_click call.

Instead of entering D (assuming that is the Calendar1 date) into line 3 and 4 just put that in the following line: (so take out line 3 and 4):

NumSemaine = IsoWeekNumber(D)

And it should work.
If the Calendar1 returns value as a date - you could just write NumSemaine = IsoWeekNumber(Calendar1.Value)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

You can also tyr using buildin Excel funtion WEEKNUM().
  NumSemaine = WorksheetFunction.WeekNum(D)

Or Format. :

I assumed "D" is your date.
Wilder1626Author Commented:
Hello all

Thanks, i will go with this one.

Now it work pretty good.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.