5 working days of a week

[deepika :excel help]

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

 

[Niek Otten]

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: Tools>Add-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.
Data>Validation, 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


| 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

 

[Niek Otten]

Oops!

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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: Tools>Add-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.
| Data>Validation, 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
|
|
| || 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
|
|

 

[Herbert Seidenberg]

How about Pinksteren?
http://www.freefilehosting.net/download/3b9hh

 

[Niek Otten]

That is why I referred to the option of a list of holidays.
Don't forget that different countries celebrate different holidays. For example, "second Christmas day" is not celebrated in
France and Belgium. I think the second Whitsuntide day (2e Pinksterdag) is not celebrated in the UK. There are many more examples.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| How about Pinksteren?
| http://www.freefilehosting.net/download/3b9hh