ExcelBanter - Problem with formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Problem with formula (https://www.excelbanter.com/excel-discussion-misc-queries/124631-problem-formula.html)

Nleric

Problem with formula

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

Problem with formula

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

"Nleric" wrote in message ...
| 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.
|

Mike

Problem with formula

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.

Nleric

Problem with formula

Hi,
thanks for your reply, I got 2006_w53 also but was expecting 2007_w01,
Regards,
Noel

"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.

Nleric

Problem with formula

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.

Niek Otten

Problem with formula

tom

Problem with formula

If M2 contains 1/1/07, then WEEKNUM(M2-1) will return the week number for
12/31/06, which is 53. Why are you subtracting 1 from the date?

Tom

"Nleric" wrote:

Hi,
thanks for your reply, I got 2006_w53 also but was expecting 2007_w01,
Regards,
Noel

"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.

Nleric

Problem with formula

Thanks for your help
Regards,
Noel

"Tom" wrote:

If M2 contains 1/1/07, then WEEKNUM(M2-1) will return the week number for
12/31/06, which is 53. Why are you subtracting 1 from the date?

Tom

"Nleric" wrote:

Hi,
thanks for your reply, I got 2006_w53 also but was expecting 2007_w01,
Regards,
Noel

"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.

All times are GMT +1. The time now is 09:03 AM.