Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
weeknum gone ?? | Excel Worksheet Functions | |||
Weeknum | Excel Worksheet Functions | |||
WEEKNUM() | Excel Discussion (Misc queries) | |||
WeekNum ISO | Excel Worksheet Functions | |||
Weeknum | Excel Worksheet Functions |