WEEKNUM Question

[RustyR]

Hi,

In my company, 2004 has 53 weeks so Week 1 of 2005 is January 2nd.

I am trying to create a formula that checks the last number of the year like
so (D( is a date in the form of 1/1/2005):
=IF((RIGHT(D9)=5),(WEEKNUM(D9,1)-1),WEEKNUM(D9,1))

But the problem is, RIGHT(D9) gives me a 0???

Any ideas??

Thank you in advance.

Rusty

 

[Frank Kabel]

Hi
you can't use text functions on a date value
try
=IF(RIGHT(TEXT(D9,"YYYY"),1)="5",WEEKNUM(D9,1)-1,WEEKNUM(D9,1))

 

[Guest]

Hi,

Use this formula...It converts the year in D9 to text then takes the
right character, the five.
=RIGHT(TEXT(D9,"YYYY"),1)
Hope it helps.
Eloy

 

[Don Wiss]

Use this formula...It converts the year in D9 to text then takes the
right character, the five.
=RIGHT(TEXT(D9,"YYYY"),1)

That would work, but not the way I would do it. I'd use:
=MOD(YEAR(D9),10)

Don <donwiss at panix.com>.

 

[Guest]

Hi Rusty.
Even though Week 1 of 2005 is January 3rd in my company (Week starting
monday), I have found help here:
http://www.cpearson.com/excel/weeknum.htm
Using a Formula for Excel that establishes the correct weeknumber using
ISO8601:2000 standard notatation, as well as VBA code for the same problem.
Best regards....and enjoy.

 

[Ron de Bruin]

Hi

Look here also for a file with a Week calendar for a the following week
number systems
http://www.rondebruin.nl/weeknumber.htm

1) ISO Week numbering: Week 1 starts on Monday of the week with the first
Thursday of the Calendar Year.

2) Excel WEEKNUM function (optional second argument of 1 (default)). Week 1
starts 1-Jan with subsequent weeks starting on a Sunday and final week
ending on 31-Dec.

3) Excel WEEKNUM function (optional second argument of 2). Week 1 starts
1-Jan with subsequent weeks starting on a Monday and final week ending on
31-Dec.

4) Simple week number. Week 1 starts on the first day of the year.