weeknum() in reverse

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

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.
 
=DATE(YEAR(TODAY()),1,1)+(7-WEEKDAY(DATE(YEAR(TODAY()),1,1))-6)+(C1*7)

where C1 is the week of the year
 
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 said:
=DATE(YEAR(TODAY()),1,1)+(7-WEEKDAY(DATE(YEAR(TODAY()),1,1))-6)+(C1*7)

where C1 is the week of the year
Click to expand...
 
But he said WEEKNUM, so an ISO standard is not applicable.

--
__________________________________
HTH

Bob

GB said:
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.
Click to expand...
 
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.
 
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.
 
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 said:
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.
Click to expand...
 
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 said:
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.
Click to expand...
 
It works very well! Thanks to all.
Lee

Bob Phillips said:
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)
Click to expand...
 
Back
Top