Calculating the week number in Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nimmi Srivastav - 20 Apr 2008 14:07 GMT

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

Reply to this Message

Ron de Bruin - 20 Apr 2008 14:22 GMT

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

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

>I am using the following formula to compute the week number based on
> the ISO standard ISO8601:2000
[quoted text clipped - 10 lines]
> Thanks,
> NS

Reply to this Message

Niek Otten - 20 Apr 2008 14:28 GMT

Read this:

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

Signature

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
[quoted text clipped - 10 lines]
| Thanks,
| NS

Reply to this Message

Ron de Bruin - 20 Apr 2008 14:32 GMT

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

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> Read this:
>
[quoted text clipped - 14 lines]
> | Thanks,
> | NS

Reply to this Message

Shane Devenshire - 20 Apr 2008 16:53 GMT

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

> I am using the following formula to compute the week number based on
> the ISO standard ISO8601:2000
[quoted text clipped - 10 lines]
> Thanks,
> NS

Reply to this Message

Mais qui est Paul - 22 Apr 2008 23:15 GMT

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

Signature

--
@+
;o)))

Reply to this Message

Rate this thread: