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

 

[Niek Otten]

Read this:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|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]

Seems the OP not reed the whole MSDN article from me Niek

 

[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

 

[Mais qui est Paul]

Bonsour® Nimmi Srivastav avec ferveur ;o))) vous nous disiez :

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)

;o)))
a shorter one :
=INT(MOD(INT((B4-2)/7)+0.6,52+5/28))+1

only valid till year 2100
not valid with 1904 calendar option