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.

Thanks in advance



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.