• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Display tasks worked on between any 2 dates

AK

New Member
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
 

Hui

Excel Ninja
Staff member
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

New Member
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

New Member
Hello Hui


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


Thanks and Regards

AK
 

dan_l

Active Member
The frickin' 90's.


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


:)
 
Top