Is 4th January 2010 = Week 2??
in [General]
|
Prev: Alerts....
Next: Shortcut key to 'Clear All'
From: nginhong on 4 Jan 2010 01:31 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
From: Mike H on 4 Jan 2010 06:42 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
From: "David Biddulph" groups [at] on 4 Jan 2010 06:57 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" <nginhong(a)discussions.microsoft.com> wrote in message news:AAC79F99-C737-41CE-B7F8-CDF6B98533F3(a)microsoft.com... > 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
From: Tor on 18 Jan 2010 07:27 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.excel.misc/Is-4th-January-2010-Week-2
From: Suchitra on 18 Jan 2010 23:06
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.excel.misc/Is-4th-January-2010-Week-2 |