Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating dates from a calender week value
Dear friends,
I have a value let's say KW12 (calender week 12) in year 2009 and I want excel to list all the dates that falls under KW12. Please advice! Brgds, LS |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating dates from a calender week value
Hi,
I would look at the WEEKNUM function. This is an ATP function in 2003 or earlier. So you need to choose Tools, Add-ins and check the Analysis ToolPa. then look at the Hep for this function. You will need to define what you mean by calendar week, this varies from country to country. You will also need to tell us what will trigger the display of the days in the desired week. Are you entering KW12 in cell A1? Or what? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lalit Sharma" wrote: Dear friends, I have a value let's say KW12 (calender week 12) in year 2009 and I want excel to list all the dates that falls under KW12. Please advice! Brgds, LS |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating dates from a calender week value
Try (for the year 2009)
=DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+1 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+2 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+3 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+4 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+5 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+6 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+7 and format the cells as Dates with the week number in A1... "Lalit Sharma" wrote: Dear friends, I have a value let's say KW12 (calender week 12) in year 2009 and I want excel to list all the dates that falls under KW12. Please advice! Brgds, LS |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating dates from a calender week value
Dear Sheeloo,
Thanks a ton for your kind assistance. It answered my purpose very well and I am really delighted :-)) Brgds, LS "Sheeloo" wrote: Try (for the year 2009) =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+1 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+2 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+3 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+4 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+5 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+6 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+7 and format the cells as Dates with the week number in A1... "Lalit Sharma" wrote: Dear friends, I have a value let's say KW12 (calender week 12) in year 2009 and I want excel to list all the dates that falls under KW12. Please advice! Brgds, LS |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating dates from a calender week value
You are most welcome. Thanks for your feedback Lalit.
If the first formula is in first row then you can use =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+($A$1-1)*7+ROW() and copy it down till the seventh row... ROW() gives 1 for row 1, 2 for row2... You can also use Year(today()) instead of 2009 if you want this to work next year too :-) "Lalit Sharma" wrote: Dear Sheeloo, Thanks a ton for your kind assistance. It answered my purpose very well and I am really delighted :-)) Brgds, LS "Sheeloo" wrote: Try (for the year 2009) =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+1 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+2 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+3 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+4 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+5 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+6 =DATE(2009,1,1)-WEEKDAY(DATE(2009,1,1))+(A1-1)*7+7 and format the cells as Dates with the week number in A1... "Lalit Sharma" wrote: Dear friends, I have a value let's say KW12 (calender week 12) in year 2009 and I want excel to list all the dates that falls under KW12. Please advice! Brgds, LS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert regular Date to Week Ending or Week Beginning Dates | Excel Discussion (Misc queries) | |||
Generating Calender in Excel giving start & end dates | Excel Worksheet Functions | |||
Work Week calender | Excel Discussion (Misc queries) | |||
Generating Calender Automatically | New Users to Excel | |||
Determining Dates Within A Calender | Excel Worksheet Functions |