Is 4th January 2010 = Week 2??

[nginhong]

Dear all,

Happy New Year!

I am using the following formula to show week number
="WEEK " & WEEKNUM(NOW(),1)

However I notice today 4th January 2010 turns out to be WEEK 2 and the
correct WEEK number should be 1 instead.

Do you think there is something wrong with the formula?
Also how to show correct week number from a date like 4th January 2010 is
equal to W101 or 10W1?

Your support is greatly appreciated.

Kind regards,
Ngin Hong

 

[Mike H]

Look in Excek help for weeknum and you'll see why. If you want the ISO week
number use Chip pearson's function

Public Function IsoWeekNumber(InDate As Date) As Integer
Dim D As Long
D = DateSerial(Year(InDate - Weekday(InDate - 1) + 4), 1, 3)
IsoWeekNumber = Int((InDate - D + Weekday(D) + 5) / 7)
End Function


Mike

 

[David Biddulph]

Yes, with either of the options for Excel's WEEKNUM function, 4th Jan 2010
is in week 2. Jan 1 was in week 1, and the new week starts on either Sunday
or Monday.

If you want a week number that is one less than Excel's week number, so that
January 1st is in week zero, you could use =WEEKNUM(TODAY())-1
If you want some different definition of week number, tell us your
definition and we can tell you the formula.
You may find some useful information at
http://www.cpearson.com/Excel/WeekNumbers.aspx or
http://www.rondebruin.nl/weeknumber.htm

 

[Tor]

This one will do the trick for an ISO WEEK number in the cell:
=INT((L2-DATE(YEAR(L2-WEEKDAY(L2-1)+4);1;3)+WEEKDAY(DATE(YEAR(L2-WEEKDAY(L2-1)+4);1;3))+5)/7)

 

[Suchitra]

Hi... But 03-Jan-2010 is week01 but when we apply the above mentioned formula it shows - week 53

 

[itsme]

this should work as well: =WEEKNUM(NOW()) -
Hope this helps!