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

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

"nginhong" wrote:

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

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
--
David Biddulph

"nginhong" wrote in message
...
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

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)

---
frmsrcurl: http://msgroups.net/microsoft.public...ry-2010-Week-2

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


---
frmsrcurl: http://msgroups.net/microsoft.public...ry-2010-Week-2

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

---
frmsrcurl: http://msgroups.net/microsoft.public...ry-2010-Week-2