Is 4th January 2010 = Week 2??

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
0
Utf
1/4/2010 6:31:01 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
1549 Views

Similar Articles

[PageSpeed] 4

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
0
Utf
1/4/2010 11:42:01 AM
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@discussions.microsoft.com> wrote in message 
news:AAC79F99-C737-41CE-B7F8-CDF6B98533F3@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 


0
David
1/4/2010 11:57:14 AM
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)
0
Tor
1/18/2010 12:27:58 PM
Hi... But 03-Jan-2010 is week01 but when we apply the above mentioned formula it shows - week 53
0
Suchitra
1/19/2010 4:06:57 AM
this should work as well: =WEEKNUM(NOW()) -1
Hope this helps!
0
itsme
1/28/2010 9:42:47 AM
Reply:

Similar Artilces:

Closing Outlook 2000 #2
Anybody ever have problems when closing Outlook 2000 while using Word as their email editor? Any way around it? I did find a kb article stating this was an issue but my "high profile" users insist on using Word as their email editor of choice...anybody know a way around this? Thanks, Dez ...

Outlook 2010 IMAP Gmail
I thought I'd give Office 2010 beta a spin (I'm running W7 Dell x86 Office 2007) and after it had migrated my email accounts I couldn't get POP3 access to any of my Gmail accounts with Outlook. It seemed to only allow IMAP access. Anyway having enabled IMAP it downloaded several thousand messages that I didn't want and from then on went into 'Synchronising Folders' for the gmail accounts for an eternity - well I gave up after 27 minutes. I restarted and tried again but it just hangs on this synchronising. finally I gave up and went back to 2007 which ha...

Danish proofing tools office 2010
Where do I find Danish Proofing Tools for Office 2010? Best to ask here in the Office 2010 Beta forum http://social.technet.microsoft.com/Forums/en-US/office2010/threads -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "Jens Axelgaard" <Jens Axelgaard@discussions.microsoft.com> wrote in message news:598A194A-F17B-44F8-A102-DE8B60CDBC40@microsoft.com... > Where do I find Danish Proofing Tools for Office 2010? ...

Visio Partner Webcast Series
Understanding the impact of new applications and hardware updates on today's complex networks is an important part of IT & Network Management. July's Visio partner webcast features two partners with IT & Network Management solutions that incorporate the power of Visio into their solutions to help keep business networks performing optimally. Join us on Tuesday, July 18th at 9am PST for this informative webcast on IT & Network Management Solutions. Partner Profiles Sysoft (www.sysoft-fr.com) Sysoft provides Amarco, a solution for Enterprise Architecture analysis and visua...

MS Messenger #2
I've got this nasty little icon in my tray for MS Messenger. Can anyone tell me how to delete "Messenger" from my computer. ...

Exchange Tool v.2
I installed the new version of the tool and now I get "Invalid Server. Please make sure you have entered a valid server name." when trying to run msextemzdfg.exe, I am copy and pasting from ADSIEDIT. And ideas? On Fri, 23 Feb 2007 09:27:15 -0800, BM <BM@discussions.microsoft.com> wrote: >I installed the new version of the tool and now I get "Invalid Server. >Please make sure you have entered a valid server name." when trying to run >msextemzdfg.exe, I am copy and pasting from ADSIEDIT. >And ideas? Which tool is this? Yeah, they changed it. You d...

Outlook 2010
how do you import you email addresses into Outlook 2010 from Outlook 2003 You don't import Outlook data. Your Outlook data file is perfectly compatible. Copy it and use it. -- Russ Valentine "joyoooo" <joyoooo@discussions.microsoft.com> wrote in message news:FA385DB1-9ADC-41F4-A70A-CEDE0A51CAAF@microsoft.com... > how do you import you email addresses into Outlook 2010 from Outlook 2003 ...

customer vendor consolidation #2
I would like to see the document number used when transferring documents from payables to receivables be the invoice/document number instead of the voucher number. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/New...

Format date in excel 2000 like '31st January 2005' #6
Trying to set up field in Excel Data Source file so that the current date can be input in letters typed in this format '31st January 2005' Is it possible or am I stuck with dd/mmm/yy format? Sorry for the repeated posts but I received a message that the system was unable to send post. Can any unanswered ones be deleted please. "Malcolm Agingwell" wrote: > Trying to set up field in Excel Data Source file so that the current date can > be input in letters typed in this format '31st January 2005' > > Is it possible or am I stuck with dd/mmm/yy format?...

Date displayed problem in my CRM 1.2 Spanish
All date for activities list in My CRM Web session displays day and "/mm/aaaa" Maybe check your PC settings? "Guiovav" wrote: > All date for activities list in My CRM Web session > displays day and "/mm/aaaa" > >-----Original Message----- >Maybe check your PC settings? > >"Guiovav" wrote: > >> All date for activities list in My CRM Web session >> displays day and "/mm/aaaa" >> >. >No, I changed my Server and Pc settings but the problem is the same. Guiovav, Try and change the da...

Audit Trail #2
I'm looking for a report that shows the creator, approver and poster of batches on the general ledger. I've looked within Great Plains and found nothing. I located this sql table (PM80100) that contains the creator and approver of purchasing batches but I've been unsuccessful with a similar one for the general ledger. Any leads would be very helpful. Thanks! Possibly in FRx? "maatthias" wrote: > I'm looking for a report that shows the creator, approver and poster of > batches on the general ledger. I've looked within Great Plains and found &g...

How to set Excel 2010 as default for open excel files instead of 2
I recently installed Office 2010 Beta. When i click on an excel file it automatically opens in Excel 2007. How do i make Excel 2010 default when i open an excel file? Try re-registering xl2010 --Close excel (all versions) --From Run (Windows Start Button) type your path to excel2010 "C:\Program Files\Microsoft Office\OFFICE14\excel.exe" /regserver and Hit Enter -- Jacob "Steven" wrote: > I recently installed Office 2010 Beta. When i click on an excel file it > automatically opens in Excel 2007. How do i make Excel 2010 default when ...

Top 2 per Group
Access 2007 - I'm trying to randomly select 2 claims per examiner. My first query successfully creates a RandomSeed field that contains a unique number for each Examiner/Claim record. However, I'm having trouble with my second query: SELECT tblRandomRec.Examiner, tblRandomRec.Claim, tblRandomRec.RandomSeed FROM tblRandomRec WHERE (((tblRandomRec.RandomSeed) In (SELECT TOP 1 RandomSeed from tblRandomRec as RR WHERE rr.claim=tblrandomrec.claim GROUP BY randomseed))) ORDER BY tblRandomRec.Examiner, tblRandomRec.Claim; What I want to end up with is 2 claims for each exam...

Derived OnDraw #2
Sorry about the previous message. I am wondering if I can use a OnDraw() function from a derived View class in a document/view project or do I need to send a message to the origninal OnDraw function in the view class. The reason I ask, is that I think that I read something about having to use the OnDraw function in the view class so I am not sure. -- Just Al "Al" <Al@discussions.microsoft.com> wrote in message news:15CC2E4D-A017-4634-A9A2-4E937C9B71BD@microsoft.com... > Sorry about the previous message. > I am wondering if I can use a OnDraw() function from a deriv...

Default Paper size #2
Hi I am in the UK and am Having problems with publisher. My Old machine would automatically default to A4 size paper but this new machine seems to love Letter size. I need publisher to default to A4or else this creates problems with my printer etc. I can change the paper size in the Printers section and choosing A4 but I need it to default to A4 Anyone any ideas? Emma Refer your second post. I replied there. -- ...

Open Level 2 files
In Outlook 2002, is there anyway to to open level 2 files from outlook? It is a real PITA to save it, find it, then open it. Thanks. ************************************************ Flip the words on the domain name to send email. ************************************************ http://www.slipstick.com/outlook/esecup/getexe.htm should help you out. AFAIK, no, but reading this page might help. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer: Curt Bates <crbates@footbig.com> aske...

Error 2203 upgrading Office 2007 to RTM Office 2010 from Technet
I downloaded the 32bit installer for Office 2010 from Technet and tried to upgrade my Office Enterprise 2007 installation. The install fails with this message: 'Error 2203. An internal error has occurred. (C:\Windows\Installer\93dca91.ipi -2147287035 ) Anyone know how to fix this? Ron You cannot upgrade an Office Enterprise. You need to purchase a new license with a new install. This applies also to TecNet and MSDN subscriptions and all HUP installations. Basically that is what the error is telling you -- Peter Please Reply to Newsgroup for...

Graph where source data has different dates #2
I'm trying to do a comparision line graph with two series of data. The are both tracked by date with the same range (from April 2008 to April 2009). However one series has data monthly and the other has data weekly. When I try to combine on a graph the dates just don't line up. Is it possible to have these line up correctly. I've tried formatting the axis to days instead of months but it appears to only line up data points. So for the data reported monthly, it is graphed across the first 12 days. Hopefully that makes sense. If not, just let me know & I'll try t...

internet addresses #2
Does anyone know what table in SQL that e-mail addresses for vendors are stored in? Thanks. Glen, Execute the following query against your company database: select * from SY01200 where Master_Type = 'VEN' You will need to link to the PM00200 by Master_ID. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "Glenn" wrote: > Does anyone know what table in SQL that e-mail addresses for vendors are > stored in? > > Thanks. > > > ...

Cannot view downloaded transactions #2
I have used Money successfully for years but just recently have had problems viewing downloaded transactions. Any ideas??? I uninstalled the program and reinstalled it. That did not solve the problem apparently because the problem lies in the details of my file. I did run into a file that would not download due "to an error with your program" (Money). I had downloaded from this site many times before. It seems that this current problem has started since that day. I get the notice that my download was successful and it tells me how many items to review. I JUST CAN&#...

Passport Sign-In Problems #2
I have a valid MSN passport sign in that works great outside of Money. When I try to add new accounts in Money, it says I have to enter my passport credentials. When I do this, it says my information cannot be verified. Any ideas? Help! ...

Junk Email filter #2
When have the Permanently delete suspected junk e-mail checked, does thie read the restricted list? What do you mean? Can you please rephrase? If you choose this option, all suspected junk email will be permanently deleted. It will bypass the Junk Email folder. -- Patricia Cardoza Outlook MVP www.cardozasolutions.com Author, Special Edition Using Microsoft Outlook 2003 ***Please post all replies to the newsgroups*** "PK" <anonymous@discussions.microsoft.com> wrote in message news:70d501c3e6a9$c9bbb790$a601280a@phx.gbl... > When have the Permanently delete suspected ju...

Vista antispyware 2010
Can some one tell me where to find information on getting "Vista Antispyware 2010" off my computer? I don't know how I got this problem but I am having trouble finding a way to remove it and it is driving me crazy. I have trend micro internet security pro but it didn't seem to pick it up. I need help badly!!! Thanks, Boria wrote: > Can some one tell me where to find information on getting "Vista > Antispyware 2010" off my computer? I don't know how I got this > problem but I am having trouble finding a way to remove it and it is ...

Last full week
Hi everyone, I'm trying to figure out how to pull data, using a QUERY, for the last full week. I tried to use a function as a criteria, but that didn't work out so well... either I was doing it wrong, or it's not a supported function of A2k3. I did figure out how to do last full month, but the solution to last full week seems to be escaping me... Between DateSerial(Year(Now()),Month(Now())-1,1) And DateSerial(Year(Now()),Month(Now()),1) Thanks for you help! Mike I'm not sure you and I have the same definition for "last full week"! What do you mean? Have y...

4th with 7th and with 8th
Hello All, Some project done by VS 6.0 uses several third-party controls made with MFC 4.0, so when I compile and run it by VS 7.0 I get mfc40.dll , mfc42.dll , mfc71d.dll loaded and _CrtDbgBreaks at AfxGetModuleThreadState inside GetDlgItem; when I compile and run it by VS 8.0 I do not have these errors. Compiler and linker options are the same. What does this really mean? Is MFC 8.0 more compatible with MFC 7.0 ? Can I fix that problems in VS 7.0 project? Can I be sure that I will not have such problems later in the VS 8.0 project? Aleksey. If I were you I would convert all of m...