Microsoft Excel - Formula for - if date is greater than or less than

Hi,

I have a database of employees where column D is their date of joining (in dd-mmm-yyyy format).

I need a formula to arrive at the given values at E1 (Column representing January 2011)

If D1<31,12,2010,0
If D1>31,1,2011,0
If D1 is between 1,1,2011 and 31,1,2011, 10.

Similarly I also need the formula to arrive at the given values in F1 (Column representing February 2011)

If D1<31,12,2010,0
If D1>28,2,2011,0
If D1 is between 1,2,2011 and 28,2,2011, 9.
If D1 is between 1,1,2011 and 31,1,2011, E1.

I need this formula to be copied for each month in G1,H1,etc respectively.

Thanks in advance

Hi
I'm not sure exactly what your trying to do so I'll give you some samples.
=IF(D1<DATE(2010,6,1),"Yes","No")-----=IF(D1>DATE(2010,6,1),"Yes","No")
If your dates are real Excel dates, you can use cell reference like this :=IF(D1<E1,"Yes","No")
=IF(AND(C3>A3,C3<B3),"Yes","No") --or --=IF(AND(B3>DATE(2010,8,10),B3<DATE(2011,5,10)),"Yes","No")
Hope you can work with that.
John

If you can insert a header in row 1, I have a formula that will do what you want:

	Jan-11	Feb-11	Mar-11	Apr-11	May-11
9/1/2010	0	0	0	0	0
10/1/2010	0	0	0	0	0
11/1/2010	0	0	0	0	0
12/1/2010	0	0	0	0	0
1/1/2011	10	10	10	10	10
2/1/2011	0	9	9	9	9
3/1/2011	0	0	8	8	8
4/1/2011	0	0	0	7	7
5/1/2011	0	0	0	0	6
6/1/2011	0	0	0	0	0
7/1/2011	0	0	0	0	0
8/1/2011	0	0	0	0	0
9/1/2011	0	0	0	0	0

The formula in E2:

=IF($D2>EOMONTH(E$1,0),0,IF($D2<E$1,IF($D2<$E$1,0,11-MONTH($D2)),11-MONTH(E$1)))

It will autofill down and right to fill out the array.  The header dates are Jan 1, 2011, Feb 1, 2011, etc formatted as mmm-yy.

It could work with the header row anywhere, just build it like this then move the header to somewhere more convenient.