Display tasks worked on between any 2 dates

[AK]

Hello


I have to prepare a report on the tasks that were worked on between 2 dates.


The data that I have is a set of tasks alongwith their start and end dates e.g.


Task Start date End date

Task1 15-Jan-2010 28-Jan-2010

Task2 20-Jan-2010 24-Jan-2010

...


The report that needs to be generated is as follows:


Given 2 dates, give the tasks that were worked on between those dates e.g. if the dates are 25-Jan-10 and 29-Jan-10, the report should only contain Task 1. But if the dates are 21-Jan-10 to 23-Jan-2010, the report should contain Task 1 and Task 2.


Could anyone please help me with this problem?


Greatly appreciate any help.


Thanks and Regards

AK

 

[xld]

Try


=SUMPRODUCT(--(C2:C20>=start_date),--(B2:B20<=end_date))

 

[Hui]

I would add a helper column D

in D2 i would have a simple =if(and(StartDate<C2,EndDate>B2),1,0)

Then either use a Pivot Table or setup a Rank column and some Lookups to retrieve the fields you want:


I've mocked up a sample here

http://rapidshare.com/files/423654496/AK_report.xlsx

 

[AK]

Hello Hui


Thanks a lot for the help.


You solved my problem. Also I learnt another use of RANK.


Thanks you very much.


Regards

AK

 

[AK]

Hello Hui


Why have you used the '+' sign before the 'RANK' formula?


Thanks and Regards

AK

 

[Hui]

It's an old habit from the early nineties

There's no need to

 

[dan_l]

The frickin' 90's.


Hui apparently used to excel to calculate how many heavy rocks it would take to make the pyramids.....