Generating dates from a calender week value

[Lalit Sharma]

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

 

[Shane Devenshire]

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?

 

[Sheeloo]

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]

Dear Sheeloo,

Thanks a ton for your kind assistance. It answered my purpose very well and
I am really delighted )

Brgds, LS

 

[Sheeloo]

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