WEEKNUM function

[david p]

ISO standard says Jan. 3, 2005 is week 1 day 1. Excel says
it is week 2 day 1.

 

[JE McGimpsey]

IIRC, XL's WEEKNUM() function predates the ISO standard...

See

http://cpearson.com/excel/weeknum.htm

 

[Norman Harker]

Hi David!

ISO8601:2000 replaced an earlier ISO but that was around 1992. Excel
was in existence before that (Version 1 for Mac 1985, Version 2 for
Windows 1987).

To get the weeknumber for ISO compliance use:

Function ISOWEEKNUM(d1 As Date)
'Laurent Longre function
Dim Test As Long
Test = DateSerial(Year(d1), Month(d1), Day(d1))
If Test <= 0 Or Test > 401769 Then
ISOWEEKNUM = CVErr(xlErrNA)
Exit Function
End If
Dim d2 As Long
d2 = DateSerial(Year(d1 - WeekDay(d1 - 1) + 4), 1, 3)
ISOWEEKNUM = Int((d1 - d2 + WeekDay(d2) + 5) / 7)
End Function

Or use the formula:

Chip Pearson: Evert van den Heuvel Formula:
=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+WEEKDAY(DATE(YEAR(A1+4-W
EEKDAY(A1+6)),1,3)))/7)

For more see:
http://www.cpearson.com/excel/weeknum.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ron Rosenfeld]

To get the weeknumber for ISO compliance use:

Function ISOWEEKNUM(d1 As Date)
'Laurent Longre function
Dim Test As Long
Test = DateSerial(Year(d1), Month(d1), Day(d1))
If Test <= 0 Or Test > 401769 Then
ISOWEEKNUM = CVErr(xlErrNA)
Exit Function
End If
Dim d2 As Long
d2 = DateSerial(Year(d1 - WeekDay(d1 - 1) + 4), 1, 3)
ISOWEEKNUM = Int((d1 - d2 + WeekDay(d2) + 5) / 7)
End Function

Or, a bit shorter:

=====================
Function ISOWeeknum(dt As Date) As Integer
ISOWeeknum = Format(dt, "ww", vbMonday, vbFirstFourDays)
End Function
=====================

)


--ron

 

[Norman Harker]

Hi Ron!

Can you modify the approach?

Your:
=ISOWEEKNUM("29-Dec-2003")
Returns 53 which is not right.

29-Dec-2003 was ISO Week 1; it was the Monday of the week containing 4-Jan /
the Monday of the week with the first Thursday in the calendar year.

Also you have a week 53 with only 1 day whereas all ISO weeks have 7 days.
And Week 1 has only 6 days.
--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Peo Sjoblom]

I use this function by Daniel Maher

Function ISOWeekNum(d1 As Date) As Integer
Dim d2 As Long
d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
ISOWeekNum = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function

 

[Norman Harker]

Hi Peo!

I'm sure you know, but just to confirm that it tests OK!

Neat logic!

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ron Rosenfeld]

Hi Ron!

Can you modify the approach?

Your:
=ISOWEEKNUM("29-Dec-2003")
Returns 53 which is not right.

29-Dec-2003 was ISO Week 1; it was the Monday of the week containing 4-Jan /
the Monday of the week with the first Thursday in the calendar year.

Also you have a week 53 with only 1 day whereas all ISO weeks have 7 days.
And Week 1 has only 6 days.

Good pickup, Norman.

Some research reveals that the error is due to a bug in Oleaut32.dll.

Probably the simplest modification would be to check for the weeknumber being
incorrect, and then correct it. So let me propose:

=======================
Function ISOWeeknum(dt As Date) As Integer
ISOWeeknum = Format(dt, "ww", vbMonday, vbFirstFourDays)
If ISOWeeknum > 52 Then
If Format(dt + 7, "ww", vbMonday, vbFirstFourDays) = 2 Then
ISOWeeknum = 1
End If
End If
End Function
======================


--ron

 

[david p]

thanks, guess i'll be altering several hundred formulas!