weeknum() in reverse
 

Is there a way to enter the week of the year and get the date of the year?
week 2 would return January 6, 2008
week 10 would return March 2, 2008

Thanks for any help.
--
Lee Coleman

weeknum() in reverse
 

=DATE(YEAR(TODAY()),1,1)+(7-WEEKDAY(DATE(YEAR(TODAY()),1,1))-6)+(C1*7)

where C1 is the week of the year

--
__________________________________
HTH

Bob

"Lee" wrote in message
...
Is there a way to enter the week of the year and get the date of the year?
week 2 would return January 6, 2008
week 10 would return March 2, 2008

Thanks for any help.
--
Lee Coleman

weeknum() in reverse
 

It's slightly more complicated than just that. The formula based on TODAY
means that your dates will all change on 1/1/09. Is that what the OP wants?
If not, replace the Year(Today()) part of the formula with 2008, and next
year change it to 2009, etc.

Besides that, it is not entirely clear whether weeks start on Sunday or
Monday. Also, see this note from the help file:
Important The WEEKNUM function considers the week containing January 1 to
be the first week of the year. However, there is a European standard that
defines the first week as the one with the majority of days (four or more)
falling in the new year. This means that for years in which there are three
days or less in the first week of January, the WEEKNUM function returns week
numbers that are incorrect according to the European standard.







"Bob Phillips" wrote in message
...
=DATE(YEAR(TODAY()),1,1)+(7-WEEKDAY(DATE(YEAR(TODAY()),1,1))-6)+(C1*7)

where C1 is the week of the year

--
__________________________________
HTH

Bob

"Lee" wrote in message
...
Is there a way to enter the week of the year and get the date of the
year?
week 2 would return January 6, 2008
week 10 would return March 2, 2008

Thanks for any help.
--
Lee Coleman

weeknum() in reverse
 

But he said WEEKNUM, so an ISO standard is not applicable.

--
__________________________________
HTH

Bob

"GB" wrote in message
...
It's slightly more complicated than just that. The formula based on TODAY
means that your dates will all change on 1/1/09. Is that what the OP
wants? If not, replace the Year(Today()) part of the formula with 2008,
and next year change it to 2009, etc.

Besides that, it is not entirely clear whether weeks start on Sunday or
Monday. Also, see this note from the help file:
Important The WEEKNUM function considers the week containing January 1
to be the first week of the year. However, there is a European standard
that defines the first week as the one with the majority of days (four or
more) falling in the new year. This means that for years in which there
are three days or less in the first week of January, the WEEKNUM function
returns week numbers that are incorrect according to the European
standard.







"Bob Phillips" wrote in message
...
=DATE(YEAR(TODAY()),1,1)+(7-WEEKDAY(DATE(YEAR(TODAY()),1,1))-6)+(C1*7)

where C1 is the week of the year

--
__________________________________
HTH

Bob

"Lee" wrote in message
...
Is there a way to enter the week of the year and get the date of the
year?
week 2 would return January 6, 2008
week 10 would return March 2, 2008

Thanks for any help.
--
Lee Coleman

weeknum() in reverse
 

I was thinking that it might be simpler and more flexible to manually define
the start date of week 1, then just add (C1 -1) *7 to that.



"Bob Phillips" wrote in message
...
But he said WEEKNUM, so an ISO standard is not applicable.

--
__________________________________
HTH

Bob

"GB" wrote in message
...
It's slightly more complicated than just that. The formula based on TODAY
means that your dates will all change on 1/1/09. Is that what the OP
wants? If not, replace the Year(Today()) part of the formula with 2008,
and next year change it to 2009, etc.

Besides that, it is not entirely clear whether weeks start on Sunday or
Monday. Also, see this note from the help file:
Important The WEEKNUM function considers the week containing January 1
to be the first week of the year. However, there is a European standard
that defines the first week as the one with the majority of days (four or
more) falling in the new year. This means that for years in which there
are three days or less in the first week of January, the WEEKNUM function
returns week numbers that are incorrect according to the European
standard.







"Bob Phillips" wrote in message
...
=DATE(YEAR(TODAY()),1,1)+(7-WEEKDAY(DATE(YEAR(TODAY()),1,1))-6)+(C1*7)

where C1 is the week of the year

--
__________________________________
HTH

Bob

"Lee" wrote in message
...
Is there a way to enter the week of the year and get the date of the
year?
week 2 would return January 6, 2008
week 10 would return March 2, 2008

Thanks for any help.
--
Lee Coleman

weeknum() in reverse
 

Thanks to Bob and GB for helping. I didn't realize that it would be so
complicated to go backwards or the different standards that were involved. I
have a planting calendar that hangs on my wall that is of the European
standard (didn't know it but now I do) that I use in planning crops. I also
use Excel for planning sow dates and planting dates for our greenhouse crops
and wanted the 2 to match.
Bob's forumlae puts January 6, 2008 as the first week of the year so I am
not sure what standard is applied but it is different than my wall calender
(1/1/08 is first week and 1/6/08 is the second). The formulae does the same
when GB's suggestion of replacing the Year(today()) with 2008. Is there a
change that I can make to the formulae to get it to match the wall calendar?

Thanks to both for teaching and helping me and to this group for all the
help.

"Lee" wrote in message
...
Is there a way to enter the week of the year and get the date of the year?
week 2 would return January 6, 2008
week 10 would return March 2, 2008

Thanks for any help.
--
Lee Coleman

weeknum() in reverse
 

Hi Lee

You seem to use US date format so GB's caviats will not apply. Bob's formula
will give you the results you specified with the following adjustment

=DATE(YEAR(TODAY()),1,1)+(7-WEEKDAY(DATE(YEAR(TODAY()),1,1))-6)+(C4*7)-7

Note that weeknumber 1 starts on 30/12/2007 but that is OK because 1/1/2008
is still week one.

Peter

"Lee" wrote:

Thanks to Bob and GB for helping. I didn't realize that it would be so
complicated to go backwards or the different standards that were involved. I
have a planting calendar that hangs on my wall that is of the European
standard (didn't know it but now I do) that I use in planning crops. I also
use Excel for planning sow dates and planting dates for our greenhouse crops
and wanted the 2 to match.
Bob's forumlae puts January 6, 2008 as the first week of the year so I am
not sure what standard is applied but it is different than my wall calender
(1/1/08 is first week and 1/6/08 is the second). The formulae does the same
when GB's suggestion of replacing the Year(today()) with 2008. Is there a
change that I can make to the formulae to get it to match the wall calendar?

Thanks to both for teaching and helping me and to this group for all the
help.

"Lee" wrote in message
...
Is there a way to enter the week of the year and get the date of the year?
week 2 would return January 6, 2008
week 10 would return March 2, 2008

Thanks for any help.
--
Lee Coleman

weeknum() in reverse
 

Lee,

I made the mistake of not counting the first week as week 1, I was
offsetting. Just use

=DATE(YEAR(TODAY()),1,1)+(7-WEEKDAY(DATE(YEAR(TODAY()),1,1))-6)+((C1-1)*7)

--
__________________________________
HTH

Bob

"Lee" wrote in message
...
Thanks to Bob and GB for helping. I didn't realize that it would be so
complicated to go backwards or the different standards that were involved.
I have a planting calendar that hangs on my wall that is of the European
standard (didn't know it but now I do) that I use in planning crops. I
also use Excel for planning sow dates and planting dates for our
greenhouse crops and wanted the 2 to match.
Bob's forumlae puts January 6, 2008 as the first week of the year so I am
not sure what standard is applied but it is different than my wall
calender (1/1/08 is first week and 1/6/08 is the second). The formulae
does the same when GB's suggestion of replacing the Year(today()) with
2008. Is there a change that I can make to the formulae to get it to match
the wall calendar?

Thanks to both for teaching and helping me and to this group for all the
help.

"Lee" wrote in message
...
Is there a way to enter the week of the year and get the date of the
year?
week 2 would return January 6, 2008
week 10 would return March 2, 2008

Thanks for any help.
--
Lee Coleman

weeknum() in reverse
 

It works very well! Thanks to all.
Lee

"Bob Phillips" wrote in message
...
Lee,

I made the mistake of not counting the first week as week 1, I was
offsetting. Just use

=DATE(YEAR(TODAY()),1,1)+(7-WEEKDAY(DATE(YEAR(TODAY()),1,1))-6)+((C1-1)*7)

--
__________________________________
HTH

Bob

"Lee" wrote in message
...
Thanks to Bob and GB for helping. I didn't realize that it would be so
complicated to go backwards or the different standards that were
involved. I have a planting calendar that hangs on my wall that is of the
European standard (didn't know it but now I do) that I use in planning
crops. I also use Excel for planning sow dates and planting dates for our
greenhouse crops and wanted the 2 to match.
Bob's forumlae puts January 6, 2008 as the first week of the year so I am
not sure what standard is applied but it is different than my wall
calender (1/1/08 is first week and 1/6/08 is the second). The formulae
does the same when GB's suggestion of replacing the Year(today()) with
2008. Is there a change that I can make to the formulae to get it to
match the wall calendar?

Thanks to both for teaching and helping me and to this group for all the
help.

"Lee" wrote in message
...
Is there a way to enter the week of the year and get the date of the
year?
week 2 would return January 6, 2008
week 10 would return March 2, 2008

Thanks for any help.
--
Lee Coleman