With a few concessions to how the data is displayed this could be a lot simpler, but I'll give you one to your specifications.

Make sure Analysis Toolpak is active: ToolsAdd-ins, Analysis Toolpak should be checked.

In E1:
31-12-2007
In F1:
=TEXT(E1,"dd-mmm-yyyy")&" to "&TEXT(E1+7,"dd-mmm-yyyy")

Copy E1 and F1 down, To row 53

Select D1.
DataValidation, Settings tab, in Allow: choose List. In Source, enter (or use the mouse to select in your worksheet:):
=$F$1:$F$53
Make column D wide enough to show the whole entry when you press the little downward arrow besides it.

In A1:
=DATEVALUE(LEFT(D1,11))

In A2:
=IF(WORKDAY($A$1,ROW()-1)=DATEVALUE(RIGHT($D$1,11)),"",WORKDAY($A$1,ROW( )-1))

Copy down some 5 rows

You can extend the WORKDAY() functions to include holidays. Look in HELP for details (really simple!)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"deepika :excel help" wrote in message
...
| in a column i need to have a drop down list which has dates from 31-01-2007
| to 04-01-2008 , 07-01-2008 to 11-01-2008 etc (the week begins from monday to
| friday) the concept here is that i should have only 5 working days of a week
| and the list should contain all such weeks for the year so taht when i
| select a particular range i shud get corresponding data fo rthat week.
|
| The list entries shud be of teh format 31-Dec-2008 to 4-Jan-2008 likewise....
| So when this entry is selected from the drop down list i should get the 5
| days between this range in 5 columns say column A1,A2,A3,A4,A5
| so when 07-Jan-2008 to 11-Jan-2008 is selected i shud get dates from 7th
| to 11th in the same A1 column to A5