|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Wrong Week number
Sorry still lost, if I have the date 09/01/2006, this is the Monday (or day
1) of week 2, and I apply your formula, then 09/01/2006 will become 08/01/2006 and the week should change to w01 for this date, but this is not happening , the week stays at w02? Noel "Bob Phillips" wrote: 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 |
Wrong Week number
No, if 02/01 is week 1, 09/01 is week 2 is it not?
-- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Sorry still lost, if I have the date 09/01/2006, this is the Monday (or day 1) of week 2, and I apply your formula, then 09/01/2006 will become 08/01/2006 and the week should change to w01 for this date, but this is not happening , the week stays at w02? Noel "Bob Phillips" wrote: 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 |
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 |
Wrong Week number
Hi,
thank you for staying with me on this and helping me to understand, you are correct 09/01 is week 2 but if I apply your formula, the -1 part should change 09/01 to 08/01 and that would be week 1, and this does not happen. Regards, Noel "Bob Phillips" wrote: No, if 02/01 is week 1, 09/01 is week 2 is it not? -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Sorry still lost, if I have the date 09/01/2006, this is the Monday (or day 1) of week 2, and I apply your formula, then 09/01/2006 will become 08/01/2006 and the week should change to w01 for this date, but this is not happening , the week stays at w02? Noel "Bob Phillips" wrote: 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 |
Wrong Week number
Noel,
I'll try an explain it with numbers and see if I can make my point that way. Normally, 01/01/2006 is the start of Week 1 and is a Sunday. Therefore, necessarily, 08/01/2006 is also a Sunday, and is week 2. If we want our week to start on Monday, we have to dupe the formula into thinking that the Monday is a Sunday, thus we subtract 1. Therefore, although Monday 02/01/2006 is a Monday, we take the week number of that date -1, that is 01/01/2006, which here is still week 1. But let's use a real date of 09/01/2006. That date -1 is 08/01/2006 is a Sunday, and this is week number 2. However, using a real date of 08/01/2006, date -1 is 07/01/2006 which returns a week number of 1. The main point is that we use the real date, but pretend that it is one day earlier. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Hi, thank you for staying with me on this and helping me to understand, you are correct 09/01 is week 2 but if I apply your formula, the -1 part should change 09/01 to 08/01 and that would be week 1, and this does not happen. Regards, Noel "Bob Phillips" wrote: No, if 02/01 is week 1, 09/01 is week 2 is it not? -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Sorry still lost, if I have the date 09/01/2006, this is the Monday (or day 1) of week 2, and I apply your formula, then 09/01/2006 will become 08/01/2006 and the week should change to w01 for this date, but this is not happening , the week stays at w02? Noel "Bob Phillips" wrote: 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 |
Wrong Week number
At http://www.probabilityof.com/excel.shtml#22 and
http://www.probabilityof.com/ISO8601.shtml you can find a udf (=DateToWeek and =WeektToDate) that converts weeks correctly. It's incredible that Microsoft have done this conversion incorrectly (non standard). |
Wrong Week number
Hi Bob,
got it, I finally understand, thank you for taking all of this time to help me Best Regards Noel "Bob Phillips" wrote: Noel, I'll try an explain it with numbers and see if I can make my point that way. Normally, 01/01/2006 is the start of Week 1 and is a Sunday. Therefore, necessarily, 08/01/2006 is also a Sunday, and is week 2. If we want our week to start on Monday, we have to dupe the formula into thinking that the Monday is a Sunday, thus we subtract 1. Therefore, although Monday 02/01/2006 is a Monday, we take the week number of that date -1, that is 01/01/2006, which here is still week 1. But let's use a real date of 09/01/2006. That date -1 is 08/01/2006 is a Sunday, and this is week number 2. However, using a real date of 08/01/2006, date -1 is 07/01/2006 which returns a week number of 1. The main point is that we use the real date, but pretend that it is one day earlier. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Hi, thank you for staying with me on this and helping me to understand, you are correct 09/01 is week 2 but if I apply your formula, the -1 part should change 09/01 to 08/01 and that would be week 1, and this does not happen. Regards, Noel "Bob Phillips" wrote: No, if 02/01 is week 1, 09/01 is week 2 is it not? -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Nleric" wrote in message ... Sorry still lost, if I have the date 09/01/2006, this is the Monday (or day 1) of week 2, and I apply your formula, then 09/01/2006 will become 08/01/2006 and the week should change to w01 for this date, but this is not happening , the week stays at w02? Noel "Bob Phillips" wrote: 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 |
| All times are GMT +1. The time now is 02:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com