Trying to create a formula in MSExcel 2007 (DATE based)

[rallydrvr]

So I am trying to create a formula in excel that is based off of a
start and end date.

It's probably fairly simplistic, but I am missing sme component
somewhere to finish this.

In short, I want to look at the start and end date of multiple
projects. If the it falls within the date range I would return a value
of "A" for active or if it doesn't I would return a value of "I" for
inactive.

I have about 80 projects running and I want to show a forecast of what
projects will be running each month and what won't based on start and
end dates.

Has anyone done something similar to this?

Thank you so much in advance.

Rob

 

[Jacob Skaria]

Assuming you data in the below format, with headers in 1st Row and data from
row2 use the below formula in D2..The formula checks whether the date
mentioned in the header falls between start date and end date..

=IF(AND(D$1>=$B2,D$1<=$C2),"A","I")

Copy the formula to other columns/rows. Format the header date cells as you
need.

Project Start Date End Date 5/1/2009 6/1/2009 7/1/2009
Project A 4/1/2009 12/1/2009 A A A
Project B 3/1/2009 5/1/2009 A I I
Project C 3/1/2009 5/1/2009 A I I
Project D 3/1/2009 5/1/2009 A I I