quirky Weeknum in 2005

[SteveH]

My company recognizes 53 weeks in the 2004 accounting year. This is handled
ok in Excel until I get to the end of the year. Week 53 starts on 12/26 but
is only 6 days long, then the next week (week 1) starts which is only one
day long, then on 1/2/05 week 2 starts which should be week 1 2005. How is
anyone handling this?

Steve H

 

[Frank Kabel]

Hi
maybe the following site helps you:
http://www.cpearson.com/excel/weeknum.htm

 

[Norman Harker]

Hi Steve H!

The craziness of this was recognized as early as 1988 and ISO8601:2000
is the latest iteration of the standard that covers it. Under
ISO8601:2000 week number 1 starts on the Monday of the week containing
the first Thursday of the calendar year. An alternative (equivalent)
of this definition is that week 1 starts on the Monday of the week
that contains Jan-4.

Excel doesn't support ISO8601:2000 but there is a formula you can use:

Evert van den Heuvel
=1+INT((A9-DATE(YEAR(A9+4-WEEKDAY(A9+6)),1,5)+WEEKDAY(DATE(YEAR(A9+4-WEEKDAY(A9+6)),1,3)))/7)

This works for both 1900 and 1904 Date Systems.

There are also some sub routines:

Laurent Longre Original:

Public 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

The disadvantage of ISO8601 is that you can get Week 1 of a calendar
year starting as early as December 28th of the preceding year. But the
set against this you always have weeks of 7 days and all weeks start
on a Monday.

 

[SteveH]

Thanks for the responses. Would you know how to modify either the formula or
the function to start the week on Sunday instead of Monday.

Steve

 

[Daniel.M]

Hi,

Then, your formula won't be ISO-Compliant anymore.
Assuming your first week of the year is still the week containing the 4th
January (In 2005,, first week is from Sun 2005-01-02 until Sat 2005-01-08), the
formula becomes:

=INT((A9-DATE(YEAR(A9-WEEKDAY(A9+1,3)+3),1,4)+
WEEKDAY(DATE(YEAR(A9-WEEKDAY(A9+1,3)+3),1,4)+1,3)+7)/7)

But if you don't care about a minimum of 4 days of the current year to start the
first week, then you could use (it will still always produce 7 days week but
first week always contains January 1st). In 2005, first week is from Sun
2004-12-26 until Sat 2005-01-01 :

=INT((A9-DATE(YEAR(A9-WEEKDAY(A9)+7),1,)+
WEEKDAY(DATE(YEAR(A9-WEEKDAY(A9)+7),1,1))+5)/7)

Regards,

Daniel M.

 

[SteveH]

Thanks for the formulas Dan. I'm trying to create week numbers compliant
with my company's accounting calendar.