Link to home
Start Free TrialLog in
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

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

Avatar of Wiesje
Wiesje
Flag of Netherlands image
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
ASKER CERTIFIED SOLUTION
Avatar of Wiesje
Wiesje
Flag of Netherlands image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Magadu
Magadu
Flag of Canada image

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

Or Format. :
Format(D,"ww")

I assumed "D" is your date.
 
Avatar of Wilder1626
Wilder1626
Flag of Canada image

ASKER

Hello all

Thanks, i will go with this one.

Now it work pretty good.