Wrong Week number

[Nleric]

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

 

[ANDRE.TASSEL]

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

 

[Niek Otten]

Hi Noel,

See

http://www.cpearson.com/excel/weeknum.htm

--
Kind regards,

Niek Otten

| 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

 

[Bob Phillips]

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]

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]

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]

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]

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]

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]

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)

 

[Ron de Bruin]

Hi Nleric

See
http://www.rondebruin.nl/weeknumber.htm
And
http://www.rondebruin.nl/isodate.htm

 

[Nleric]

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]

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)

 

[sandved]

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

 

[Nleric]

Hi Bob,
got it, I finally understand, thank you for taking all of this time to help me
Best Regards
Noel