Validating months between 2 dates

[Navs]

Hello Everyone - This is my first question on the forum and I was hoping the gurus here can help me with the following. Here is what I am trying to achieve:


Employee Start Date End Date Jan Feb Mar April May

A 1/12/2011 4/30/2011 1 1 1 1 0

B 2/1/2011 5/30/2011 0 1 1 1 1

C 3/1/2011 5/30/2011 0 0 1 1 1


There a 3 employees with different Start and End dates. I want to write a formula for each cell under the months titled Jan-May that will return "1" if that employee was present in that month based on the Start and End Date and 0 if he wasn't. So for example, employee B was present in Feb, Mar, April and May based on the date range, hence there is "1" in those months and 0 in Jan. Same way for the other two employees.


Can someone assist me with this formula?


Thanks

 

[Hui]

Assuming that the table starts in A1

I would change the top names to be actual dates instead of Text eg: d/m/y 1/1/2011, 1/2/2011 etc

Then in D2: =1*AND($B2<=EOMONTH(D$1,0),$C2>=(EOMONTH(D$1,-1)+1))

 

[Meedan]

After changing dates to text i.e jan to 01/01/2011 you can also use below simple formula


=IF(AND(MONTH(D$1)>=MONTH($B2),MONTH(D$1)<=MONTH($C2)),1,0)

 

[Navs]

Hui and Meedan - Thank you very much for the response.