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?
WiesjeConnect With a Mentor Commented:
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)
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 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.