| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wrong Week number
Hi,
I am in Ireland and as such Excel (2003) is putting in the wrong week numbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem? I am using the following formula, could someone explain this formula in english? =CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1))) Regards, Noel |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Wrong Week number
Nleric wrote:
Hi, I am in Ireland and as such Excel (2003) is putting in the wrong week numbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem? I am using the following formula, could someone explain this formula in english? =CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1))) Regards, Noel Try this =INT(((A1)-(DATE(YEAR((A1)+(MOD(8-WEEKDAY((A1)),7)-3)),1,1))-3+ MOD(WEEKDAY(DATE(YEAR((A1)+(MOD(8-WEEKDAY((A1)),7)-3)),1,1))+1,7))/7)+1 Put the date in cell A1 I got this formula from this forum some time ago |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Wrong Week number
Hi Noel,
See http://www.cpearson.com/excel/weeknum.htm -- Kind regards, Niek Otten "Nleric" wrote in message ... | Hi, | I am in Ireland and as such Excel (2003) is putting in the wrong week | numbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in | fact it is week 1, how can I get it too correct this problem? | I am using the following formula, could someone explain this formula in | english? | =CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1))) | | Regards, | Noel |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Wrong Week number
It is subtracting 1, so no wonder you get 0.
It should be =CONCATENATE(YEAR(BM2),"_","w",IF(WEEKNUM(BM2)-1<10,CONCATENATE("0",WEEKNUM( BM2)),WEEKNUM(BM2))) But you can simplify it and remove CONCATENATE with =YEAR(BM2)&"_w"&TEXT(WEEKNUM(BM2),"00") or even more simply as =YEAR(M2)&TEXT(WEEKNUM(M2),"""_w""00") -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Hi, I am in Ireland and as such Excel (2003) is putting in the wrong week numbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem? I am using the following formula, could someone explain this formula in english? =CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKN UM(BM2)-1)),(WEEKNUM(BM2)-1))) Regards, Noel |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Wrong Week number
Hi Bob thanks for this ,it is excellant, just one thing that I need to tweak
with it and you probably know the answer, the week here is from Monday to Sunday, so how do I include the extra day in the formula. i.e. Week one is 02/01/2006 - 08/01/2006 Thanks again, Noel "Bob Phillips" wrote: It is subtracting 1, so no wonder you get 0. It should be =CONCATENATE(YEAR(BM2),"_","w",IF(WEEKNUM(BM2)-1<10,CONCATENATE("0",WEEKNUM( BM2)),WEEKNUM(BM2))) But you can simplify it and remove CONCATENATE with =YEAR(BM2)&"_w"&TEXT(WEEKNUM(BM2),"00") or even more simply as =YEAR(M2)&TEXT(WEEKNUM(M2),"""_w""00") -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Hi, I am in Ireland and as such Excel (2003) is putting in the wrong week numbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem? I am using the following formula, could someone explain this formula in english? =CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKN UM(BM2)-1)),(WEEKNUM(BM2)-1))) Regards, Noel |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Wrong Week number
Just subtract one from the date before WEEKNUMing it
=YEAR(BM2)&TEXT(WEEKNUM(BM2-1),"""_w""00") -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Hi Bob thanks for this ,it is excellant, just one thing that I need to tweak with it and you probably know the answer, the week here is from Monday to Sunday, so how do I include the extra day in the formula. i.e. Week one is 02/01/2006 - 08/01/2006 Thanks again, Noel "Bob Phillips" wrote: It is subtracting 1, so no wonder you get 0. It should be =CONCATENATE(YEAR(BM2),"_","w",IF(WEEKNUM(BM2)-1<10,CONCATENATE("0",WEEKNUM( BM2)),WEEKNUM(BM2))) But you can simplify it and remove CONCATENATE with =YEAR(BM2)&"_w"&TEXT(WEEKNUM(BM2),"00") or even more simply as =YEAR(M2)&TEXT(WEEKNUM(M2),"""_w""00") -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Hi, I am in Ireland and as such Excel (2003) is putting in the wrong week numbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem? I am using the following formula, could someone explain this formula in english? =CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKN UM(BM2)-1)),(WEEKNUM(BM2)-1))) Regards, Noel |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Wrong Week number
Thanks again for your quick reply, I am a total beginner to this and am
slightly confused, if I use -1, then surely 02/01/2006 - 07/01/2006 should change to w00? I have tried the -1 option and it works fine but I would have expected them to change to w00. Regards, Noel "Bob Phillips" wrote: Just subtract one from the date before WEEKNUMing it =YEAR(BM2)&TEXT(WEEKNUM(BM2-1),"""_w""00") -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Hi Bob thanks for this ,it is excellant, just one thing that I need to tweak with it and you probably know the answer, the week here is from Monday to Sunday, so how do I include the extra day in the formula. i.e. Week one is 02/01/2006 - 08/01/2006 Thanks again, Noel "Bob Phillips" wrote: It is subtracting 1, so no wonder you get 0. It should be =CONCATENATE(YEAR(BM2),"_","w",IF(WEEKNUM(BM2)-1<10,CONCATENATE("0",WEEKNUM( BM2)),WEEKNUM(BM2))) But you can simplify it and remove CONCATENATE with =YEAR(BM2)&"_w"&TEXT(WEEKNUM(BM2),"00") or even more simply as =YEAR(M2)&TEXT(WEEKNUM(M2),"""_w""00") -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Hi, I am in Ireland and as such Excel (2003) is putting in the wrong week numbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem? I am using the following formula, could someone explain this formula in english? =CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKN UM(BM2)-1)),(WEEKNUM(BM2)-1))) Regards, Noel |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Wrong Week number
No, because you are subtracting 1 from the date, not the week number.
-- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Thanks again for your quick reply, I am a total beginner to this and am slightly confused, if I use -1, then surely 02/01/2006 - 07/01/2006 should change to w00? I have tried the -1 option and it works fine but I would have expected them to change to w00. Regards, Noel "Bob Phillips" wrote: Just subtract one from the date before WEEKNUMing it =YEAR(BM2)&TEXT(WEEKNUM(BM2-1),"""_w""00") -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Hi Bob thanks for this ,it is excellant, just one thing that I need to tweak with it and you probably know the answer, the week here is from Monday to Sunday, so how do I include the extra day in the formula. i.e. Week one is 02/01/2006 - 08/01/2006 Thanks again, Noel "Bob Phillips" wrote: It is subtracting 1, so no wonder you get 0. It should be =CONCATENATE(YEAR(BM2),"_","w",IF(WEEKNUM(BM2)-1<10,CONCATENATE("0",WEEKNUM( BM2)),WEEKNUM(BM2))) But you can simplify it and remove CONCATENATE with =YEAR(BM2)&"_w"&TEXT(WEEKNUM(BM2),"00") or even more simply as =YEAR(M2)&TEXT(WEEKNUM(M2),"""_w""00") -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Hi, I am in Ireland and as such Excel (2003) is putting in the wrong week numbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem? I am using the following formula, could someone explain this formula in english? =CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKN UM(BM2)-1)),(WEEKNUM(BM2)-1))) Regards, Noel |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Wrong Week number
Hi Nleric
See http://www.rondebruin.nl/weeknumber.htm And http://www.rondebruin.nl/isodate.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Nleric" wrote in message ... Hi, I am in Ireland and as such Excel (2003) is putting in the wrong week numbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem? I am using the following formula, could someone explain this formula in english? =CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1))) Regards, Noel |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for a date's week number in a month (1st, 2nd, etc.) | Excel Discussion (Misc queries) | |||
| selecting by week number?????? | Excel Discussion (Misc queries) | |||
| Dates in a week number | Excel Worksheet Functions | |||
| evaluate data by week number | Excel Worksheet Functions | |||
| how to get week number in month in excel ? | Charts and Charting in Excel | |||
Hybrid Mode
