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

Asked By Subash D on 08-Nov-11 07:16 AM
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.

John D replied to Subash D on 08-Nov-11 10:29 AM
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
wally eye replied to Subash D on 08-Nov-11 10:20 PM
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.