Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with formula
No. Read the link I posted.
-- Kind regards, Niek Otten Microsoft MVP - Excel "Nleric" wrote in message ... | 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. | |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Is it possible? | Excel Worksheet Functions | |||
formula percentage problem | New Users to Excel | |||
formula problem | New Users to Excel | |||
problem with Array Formula | Excel Worksheet Functions |