software

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),quot;_quot;,quot;wquot;,IF((WEEKNUM(BM2)-1)lt;10,CONCATENATE(quot;0quot;,(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1)))

Regards,
Noel

Nleric wrote:
gt; Hi,
gt; I am in Ireland and as such Excel (2003) is putting in the wrong week
gt; numbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in
gt; fact it is week 1, how can I get it too correct this problem?
gt; I am using the following formula, could someone explain this formula in
gt; english?
gt; =CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF((WEEKNUM(BM2)-1)lt;10,CONCATENATE(quot;0quot;,(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1)))
gt;
gt; Regards,
gt; 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) 1Put the date in cell A1I got this formula from this forum some time agoHi Noel,

See

www.cpearson.com/excel/weeknum.htm

--
Kind regards,

Niek Otten

quot;Nlericquot; gt; 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),quot;_quot;,quot;wquot;,IF((WEEKNUM(BM2)-1)lt;10,CONCATENATE(quot;0quot;,(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1)))
|
| Regards,
| Noel
It is subtracting 1, so no wonder you get 0.

It should be

=CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF(WEEKNUM(BM2)-1lt;10,CONCATENATE(quot;0quot;,WEEKNUM(
BM2)),WEEKNUM(BM2)))

But you can simplify it and remove CONCATENATE with

=YEAR(BM2)amp;quot;_wquot;amp;TEXT(WEEKNUM(BM2),quot;00quot;)

or even more simply as

=YEAR(M2)amp;TEXT(WEEKNUM(M2),quot;quot;quot;_wquot;quot;00quot;)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;Nlericquot; gt; wrote in message
...
gt; Hi,
gt; I am in Ireland and as such Excel (2003) is putting in the wrong week
gt; numbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when
in
gt; fact it is week 1, how can I get it too correct this problem?
gt; I am using the following formula, could someone explain this formula in
gt; english?
gt;
=CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF((WEEKNUM(BM2)-1)lt;10,CONCATENATE(quot;0quot;,(WEEKN
UM(BM2)-1)),(WEEKNUM(BM2)-1)))
gt;
gt; Regards,
gt; Noel
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

quot;Bob Phillipsquot; wrote:

gt; It is subtracting 1, so no wonder you get 0.
gt;
gt; It should be
gt;
gt; =CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF(WEEKNUM(BM2)-1lt;10,CONCATENATE(quot;0quot;,WEEKNUM(
gt; BM2)),WEEKNUM(BM2)))
gt;
gt; But you can simplify it and remove CONCATENATE with
gt;
gt; =YEAR(BM2)amp;quot;_wquot;amp;TEXT(WEEKNUM(BM2),quot;00quot;)
gt;
gt; or even more simply as
gt;
gt; =YEAR(M2)amp;TEXT(WEEKNUM(M2),quot;quot;quot;_wquot;quot;00quot;)
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove xxx from email address if mailing direct)
gt;
gt; quot;Nlericquot; gt; wrote in message
gt; ...
gt; gt; Hi,
gt; gt; I am in Ireland and as such Excel (2003) is putting in the wrong week
gt; gt; numbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when
gt; in
gt; gt; fact it is week 1, how can I get it too correct this problem?
gt; gt; I am using the following formula, could someone explain this formula in
gt; gt; english?
gt; gt;
gt; =CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF((WEEKNUM(BM2)-1)lt;10,CONCATENATE(quot;0quot;,(WEEKN
gt; UM(BM2)-1)),(WEEKNUM(BM2)-1)))
gt; gt;
gt; gt; Regards,
gt; gt; Noel
gt;
gt;
gt;

Just subtract one from the date before WEEKNUMing it

=YEAR(BM2)amp;TEXT(WEEKNUM(BM2-1),quot;quot;quot;_wquot;quot;00quot;)--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;Nlericquot; gt; wrote in message
...
gt; Hi Bob thanks for this ,it is excellant, just one thing that I need to
tweak
gt; with it and you probably know the answer, the week here is from Monday to
gt; Sunday, so how do I include the extra day in the formula.
gt; i.e. Week one is 02/01/2006 - 08/01/2006
gt; Thanks again,
gt; Noel
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; It is subtracting 1, so no wonder you get 0.
gt; gt;
gt; gt; It should be
gt; gt;
gt; gt;
=CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF(WEEKNUM(BM2)-1lt;10,CONCATENATE(quot;0quot;,WEEKNUM(
gt; gt; BM2)),WEEKNUM(BM2)))
gt; gt;
gt; gt; But you can simplify it and remove CONCATENATE with
gt; gt;
gt; gt; =YEAR(BM2)amp;quot;_wquot;amp;TEXT(WEEKNUM(BM2),quot;00quot;)
gt; gt;
gt; gt; or even more simply as
gt; gt;
gt; gt; =YEAR(M2)amp;TEXT(WEEKNUM(M2),quot;quot;quot;_wquot;quot;00quot;)
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove xxx from email address if mailing direct)
gt; gt;
gt; gt; quot;Nlericquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi,
gt; gt; gt; I am in Ireland and as such Excel (2003) is putting in the wrong week
gt; gt; gt; numbers in my spreadsheet, for example 02/01/2006 is listed as week 0
when
gt; gt; in
gt; gt; gt; fact it is week 1, how can I get it too correct this problem?
gt; gt; gt; I am using the following formula, could someone explain this formula
in
gt; gt; gt; english?
gt; gt; gt;
gt; gt;
=CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF((WEEKNUM(BM2)-1)lt;10,CONCATENATE(quot;0quot;,(WEEKN
gt; gt; UM(BM2)-1)),(WEEKNUM(BM2)-1)))
gt; gt; gt;
gt; gt; gt; Regards,
gt; gt; gt; Noel
gt; gt;
gt; gt;
gt; gt;
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

quot;Bob Phillipsquot; wrote:

gt; Just subtract one from the date before WEEKNUMing it
gt;
gt; =YEAR(BM2)amp;TEXT(WEEKNUM(BM2-1),quot;quot;quot;_wquot;quot;00quot;)
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove xxx from email address if mailing direct)
gt;
gt; quot;Nlericquot; gt; wrote in message
gt; ...
gt; gt; Hi Bob thanks for this ,it is excellant, just one thing that I need to
gt; tweak
gt; gt; with it and you probably know the answer, the week here is from Monday to
gt; gt; Sunday, so how do I include the extra day in the formula.
gt; gt; i.e. Week one is 02/01/2006 - 08/01/2006
gt; gt; Thanks again,
gt; gt; Noel
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; It is subtracting 1, so no wonder you get 0.
gt; gt; gt;
gt; gt; gt; It should be
gt; gt; gt;
gt; gt; gt;
gt; =CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF(WEEKNUM(BM2)-1lt;10,CONCATENATE(quot;0quot;,WEEKNUM(
gt; gt; gt; BM2)),WEEKNUM(BM2)))
gt; gt; gt;
gt; gt; gt; But you can simplify it and remove CONCATENATE with
gt; gt; gt;
gt; gt; gt; =YEAR(BM2)amp;quot;_wquot;amp;TEXT(WEEKNUM(BM2),quot;00quot;)
gt; gt; gt;
gt; gt; gt; or even more simply as
gt; gt; gt;
gt; gt; gt; =YEAR(M2)amp;TEXT(WEEKNUM(M2),quot;quot;quot;_wquot;quot;00quot;)
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove xxx from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Nlericquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Hi,
gt; gt; gt; gt; I am in Ireland and as such Excel (2003) is putting in the wrong week
gt; gt; gt; gt; numbers in my spreadsheet, for example 02/01/2006 is listed as week 0
gt; when
gt; gt; gt; in
gt; gt; gt; gt; fact it is week 1, how can I get it too correct this problem?
gt; gt; gt; gt; I am using the following formula, could someone explain this formula
gt; in
gt; gt; gt; gt; english?
gt; gt; gt; gt;
gt; gt; gt;
gt; =CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF((WEEKNUM(BM2)-1)lt;10,CONCATENATE(quot;0quot;,(WEEKN
gt; gt; gt; UM(BM2)-1)),(WEEKNUM(BM2)-1)))
gt; gt; gt; gt;
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Noel
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

No, because you are subtracting 1 from the date, not the week number.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;Nlericquot; gt; wrote in message
...
gt; Thanks again for your quick reply, I am a total beginner to this and am
gt; slightly confused, if I use -1, then surely 02/01/2006 - 07/01/2006 should
gt; change to w00?
gt; I have tried the -1 option and it works fine but I would have expected
them
gt; to change to w00.
gt; Regards,
gt; Noel
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Just subtract one from the date before WEEKNUMing it
gt; gt;
gt; gt; =YEAR(BM2)amp;TEXT(WEEKNUM(BM2-1),quot;quot;quot;_wquot;quot;00quot;)
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove xxx from email address if mailing direct)
gt; gt;
gt; gt; quot;Nlericquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi Bob thanks for this ,it is excellant, just one thing that I need to
gt; gt; tweak
gt; gt; gt; with it and you probably know the answer, the week here is from Monday
to
gt; gt; gt; Sunday, so how do I include the extra day in the formula.
gt; gt; gt; i.e. Week one is 02/01/2006 - 08/01/2006
gt; gt; gt; Thanks again,
gt; gt; gt; Noel
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; It is subtracting 1, so no wonder you get 0.
gt; gt; gt; gt;
gt; gt; gt; gt; It should be
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
=CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF(WEEKNUM(BM2)-1lt;10,CONCATENATE(quot;0quot;,WEEKNUM(
gt; gt; gt; gt; BM2)),WEEKNUM(BM2)))
gt; gt; gt; gt;
gt; gt; gt; gt; But you can simplify it and remove CONCATENATE with
gt; gt; gt; gt;
gt; gt; gt; gt; =YEAR(BM2)amp;quot;_wquot;amp;TEXT(WEEKNUM(BM2),quot;00quot;)
gt; gt; gt; gt;
gt; gt; gt; gt; or even more simply as
gt; gt; gt; gt;
gt; gt; gt; gt; =YEAR(M2)amp;TEXT(WEEKNUM(M2),quot;quot;quot;_wquot;quot;00quot;)
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove xxx from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Nlericquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; Hi,
gt; gt; gt; gt; gt; I am in Ireland and as such Excel (2003) is putting in the wrong
week
gt; gt; gt; gt; gt; numbers in my spreadsheet, for example 02/01/2006 is listed as
week 0
gt; gt; when
gt; gt; gt; gt; in
gt; gt; gt; gt; gt; fact it is week 1, how can I get it too correct this problem?
gt; gt; gt; gt; gt; I am using the following formula, could someone explain this
formula
gt; gt; in
gt; gt; gt; gt; gt; english?
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
=CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF((WEEKNUM(BM2)-1)lt;10,CONCATENATE(quot;0quot;,(WEEKN
gt; gt; gt; gt; UM(BM2)-1)),(WEEKNUM(BM2)-1)))
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; Noel
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
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

quot;Bob Phillipsquot; wrote:

gt; No, because you are subtracting 1 from the date, not the week number.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove xxx from email address if mailing direct)
gt;
gt; quot;Nlericquot; gt; wrote in message
gt; ...
gt; gt; Thanks again for your quick reply, I am a total beginner to this and am
gt; gt; slightly confused, if I use -1, then surely 02/01/2006 - 07/01/2006 should
gt; gt; change to w00?
gt; gt; I have tried the -1 option and it works fine but I would have expected
gt; them
gt; gt; to change to w00.
gt; gt; Regards,
gt; gt; Noel
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; Just subtract one from the date before WEEKNUMing it
gt; gt; gt;
gt; gt; gt; =YEAR(BM2)amp;TEXT(WEEKNUM(BM2-1),quot;quot;quot;_wquot;quot;00quot;)
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove xxx from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Nlericquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Hi Bob thanks for this ,it is excellant, just one thing that I need to
gt; gt; gt; tweak
gt; gt; gt; gt; with it and you probably know the answer, the week here is from Monday
gt; to
gt; gt; gt; gt; Sunday, so how do I include the extra day in the formula.
gt; gt; gt; gt; i.e. Week one is 02/01/2006 - 08/01/2006
gt; gt; gt; gt; Thanks again,
gt; gt; gt; gt; Noel
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; It is subtracting 1, so no wonder you get 0.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; It should be
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; =CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF(WEEKNUM(BM2)-1lt;10,CONCATENATE(quot;0quot;,WEEKNUM(
gt; gt; gt; gt; gt; BM2)),WEEKNUM(BM2)))
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; But you can simplify it and remove CONCATENATE with
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =YEAR(BM2)amp;quot;_wquot;amp;TEXT(WEEKNUM(BM2),quot;00quot;)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; or even more simply as
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =YEAR(M2)amp;TEXT(WEEKNUM(M2),quot;quot;quot;_wquot;quot;00quot;)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; HTH
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; (remove xxx from email address if mailing direct)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Nlericquot; gt; wrote in message
gt; gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; gt; Hi,
gt; gt; gt; gt; gt; gt; I am in Ireland and as such Excel (2003) is putting in the wrong
gt; week
gt; gt; gt; gt; gt; gt; numbers in my spreadsheet, for example 02/01/2006 is listed as
gt; week 0
gt; gt; gt; when
gt; gt; gt; gt; gt; in
gt; gt; gt; gt; gt; gt; fact it is week 1, how can I get it too correct this problem?
gt; gt; gt; gt; gt; gt; I am using the following formula, could someone explain this
gt; formula
gt; gt; gt; in
gt; gt; gt; gt; gt; gt; english?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; =CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF((WEEKNUM(BM2)-1)lt;10,CONCATENATE(quot;0quot;,(WEEKN
gt; gt; gt; gt; gt; UM(BM2)-1)),(WEEKNUM(BM2)-1)))
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; gt; Noel
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

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)

quot;Nlericquot; gt; wrote in message
news
gt; Sorry still lost, if I have the date 09/01/2006, this is the Monday (or
day
gt; 1) of week 2, and I apply your formula, then 09/01/2006 will become
gt; 08/01/2006 and the week should change to w01 for this date, but this is
not
gt; happening , the week stays at w02?
gt; Noel
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; No, because you are subtracting 1 from the date, not the week number.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove xxx from email address if mailing direct)
gt; gt;
gt; gt; quot;Nlericquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Thanks again for your quick reply, I am a total beginner to this and
am
gt; gt; gt; slightly confused, if I use -1, then surely 02/01/2006 - 07/01/2006
should
gt; gt; gt; change to w00?
gt; gt; gt; I have tried the -1 option and it works fine but I would have expected
gt; gt; them
gt; gt; gt; to change to w00.
gt; gt; gt; Regards,
gt; gt; gt; Noel
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Just subtract one from the date before WEEKNUMing it
gt; gt; gt; gt;
gt; gt; gt; gt; =YEAR(BM2)amp;TEXT(WEEKNUM(BM2-1),quot;quot;quot;_wquot;quot;00quot;)
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove xxx from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Nlericquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; Hi Bob thanks for this ,it is excellant, just one thing that I
need to
gt; gt; gt; gt; tweak
gt; gt; gt; gt; gt; with it and you probably know the answer, the week here is from
Monday
gt; gt; to
gt; gt; gt; gt; gt; Sunday, so how do I include the extra day in the formula.
gt; gt; gt; gt; gt; i.e. Week one is 02/01/2006 - 08/01/2006
gt; gt; gt; gt; gt; Thanks again,
gt; gt; gt; gt; gt; Noel
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; It is subtracting 1, so no wonder you get 0.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; It should be
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
=CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF(WEEKNUM(BM2)-1lt;10,CONCATENATE(quot;0quot;,WEEKNUM(
gt; gt; gt; gt; gt; gt; BM2)),WEEKNUM(BM2)))
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; But you can simplify it and remove CONCATENATE with
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =YEAR(BM2)amp;quot;_wquot;amp;TEXT(WEEKNUM(BM2),quot;00quot;)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; or even more simply as
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =YEAR(M2)amp;TEXT(WEEKNUM(M2),quot;quot;quot;_wquot;quot;00quot;)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; HTH
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; (remove xxx from email address if mailing direct)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Nlericquot; gt; wrote in message
gt; gt; gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; gt; gt; Hi,
gt; gt; gt; gt; gt; gt; gt; I am in Ireland and as such Excel (2003) is putting in the
wrong
gt; gt; week
gt; gt; gt; gt; gt; gt; gt; numbers in my spreadsheet, for example 02/01/2006 is listed as
gt; gt; week 0
gt; gt; gt; gt; when
gt; gt; gt; gt; gt; gt; in
gt; gt; gt; gt; gt; gt; gt; fact it is week 1, how can I get it too correct this problem?
gt; gt; gt; gt; gt; gt; gt; I am using the following formula, could someone explain this
gt; gt; formula
gt; gt; gt; gt; in
gt; gt; gt; gt; gt; gt; gt; english?
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
=CONCATENATE(YEAR(BM2),quot;_quot;,quot;wquot;,IF((WEEKNUM(BM2)-1)lt;10,CONCATENATE(quot;0quot;,(WEEKN
gt; gt; gt; gt; gt; gt; UM(BM2)-1)),(WEEKNUM(BM2)-1)))
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; gt; gt; Noel
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;