Problem with formula

[Guest]

Hi,
I am using Excel 2003 and am using this formula for converting dates
(21/07/2003)
to the following format 2003_w30. But for 2007 instead of giving me 2007_w01
I get 2006_w53, can some have a look at the formula and correct it?
=YEAR(M2)&TEXT(WEEKNUM(M2-1),"""_w""00")

Regards,
Noel LAckey.

 

[Niek Otten]

Hi Noel,

< I get 2006_w53>

I think you get 2007_w53.

For week numbers, visit

http://www.rondebruin.nl/weeknumber.htm


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
| I am using Excel 2003 and am using this formula for converting dates
| (21/07/2003)
| to the following format 2003_w30. But for 2007 instead of giving me 2007_w01
| I get 2006_w53, can some have a look at the formula and correct it?
| =YEAR(M2)&TEXT(WEEKNUM(M2-1),"""_w""00")
|
| Regards,
| Noel LAckey.
|

 

[Niek Otten]

No. Read the link I posted.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
| I am confused, 1/1/07 was a Monday and surely it should give 2007_w01 for
| this?
| Noel
|
| "Mike" wrote:
|
| > Hi,
| >
| > The answer your getting depends on what you have in cell M2. For 1/1/07 it
| > will correctly return week 53 and for 2/1/07 it rolls over to week 1.
| >
| > Mike
| >
| > "Nleric" wrote:
| >
| > > Hi,
| > > I am using Excel 2003 and am using this formula for converting dates
| > > (21/07/2003)
| > > to the following format 2003_w30. But for 2007 instead of giving me 2007_w01
| > > I get 2006_w53, can some have a look at the formula and correct it?
| > > =YEAR(M2)&TEXT(WEEKNUM(M2-1),"""_w""00")
| > >
| > > Regards,
| > > Noel LAckey.
| > >