Calculating the week number in Excel

[Nimmi Srivastav]

I am using the following formula to compute the week number based on
the ISO standard ISO8601:2000


=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-
WEEKDAY(B4-1)+4),1,3))+5)

(http://msdn2.microsoft.com/en-us/library/bb277364.aspx)


As per this formula, 1/1/2009 should be week 1. However, as per my
Outlook calendar 1/1/2009 through 1/3/2009 show up as week 53. Can
someone kindly explain the discrepancy?

Thanks,
NS

[Ron de Bruin]

Hi Nimmi

Outlook is not using the ISO Standard

See
http://www.rondebruin.nl/weeknumber.htm

If you download the calandar file you can see the differents
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Post by Nimmi Srivastav
I am using the following formula to compute the week number based on
the ISO standard ISO8601:2000
=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-
WEEKDAY(B4-1)+4),1,3))+5)
(http://msdn2.microsoft.com/en-us/library/bb277364.aspx)
As per this formula, 1/1/2009 should be week 1. However, as per my
Outlook calendar 1/1/2009 through 1/3/2009 show up as week 53. Can
someone kindly explain the discrepancy?
Thanks,
NS

[Shane Devenshire]

Hi Nimmi,

Assuming you are entering your dates as m/d/y then you could use the
following formula:

=INT((B2+SUM({1,-1}*MOD("1/2/"&YEAR(B2-MOD(B2-2,7)+3),{7,1E+99}))+5)/7)

or you could use a VBA function:

Public Function ISOWeekNum(mydate As Date) As Byte
D = mydate - 2
T = CDate("1/2/" & year(D - D Mod 7 + 5))
ISOWeekNum = (D - T + T Mod 7 + 4) / 7
End Function

I dimmed the variables at the Module level, so they don't show here. In the
spreadsheet the function reads =ISOWeekNum(A1) where A1 contains 1/1/2009.

Cheers,
Shane Devenshire
Microsoft Excel MVP

Post by Nimmi Srivastav
I am using the following formula to compute the week number based on
the ISO standard ISO8601:2000
=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-
WEEKDAY(B4-1)+4),1,3))+5)
(http://msdn2.microsoft.com/en-us/library/bb277364.aspx)
As per this formula, 1/1/2009 should be week 1. However, as per my
Outlook calendar 1/1/2009 through 1/3/2009 show up as week 53. Can
someone kindly explain the discrepancy?
Thanks,
NS