quirky Weeknum in 2005

  • Thread starter Thread starter SteveH
  • Start date Start date
S

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
 
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.
 
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
 
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.
 
Thanks for the formulas Dan. I'm trying to create week numbers compliant
with my company's accounting calendar.
 
Back
Top