I need to get the week number in excell from a cell with a date (dd/mm/aaaa),
but the first week of the year has 3 possiblilities, first week, with day
nº 1, first week with at least 4 days of the new year or finally the first
full week.
How to do this in excell ? I tried with weeknum() but I can't choose the
first week
Thanks.
This can be done in VBA.
Do you want to be able to select one of your three possibilities?
Or do you just want an output that conforms to the ISO standard?
If the latter, then this UDF will do that:
=====================
Function ISOWeeknum(dt As Date) As Integer
ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays)
If ISOWeeknum > 52 Then
If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then
ISOWeeknum = 1
End If
End If
End Function
=====================
If the former, then this UDF can be modified, and optional arguments added to
denote the Type of weeknumber you wish; and also the Starting date of the week.
The DatePart VBA function is very flexible in this regard.
To enter the above UDF, <alt><F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
above code into the window that opens.
To use this UDF, merely insert =ISOWeeknum(dt) into some cell where "dt" is
either an Excel date or a reference to a cell that contains a date.
--ron
)