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

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

Oops!

In E2, enter:
=E1+7
Copy down to row 53

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message ...
| 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
|
|