Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
this should work as well: =WEEKNUM(NOW()) -1
Hope this helps! --- frmsrcurl: http://msgroups.net/microsoft.public...ry-2010-Week-2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Convert regular Date to Week Ending or Week Beginning Dates | Excel Discussion (Misc queries) | |||
2010-01-04 is equal to week 2??? | Excel Discussion (Misc queries) | |||
Return day of week for 1 January | Excel Worksheet Functions | |||
Return the day of week for 1 January | About this forum | |||
How do I set up a week by week skill training schedule in excel? | Excel Discussion (Misc queries) |