Help with IF formula

  • Thread starter Thread starter Porlie
  • Start date Start date
P

Porlie

Column A is either blank or has a date
Column B has a date
Coumn C has a person's name
I want return the person's name if Column D column A is blank or greater
than a certain date and if column B is less than a certain date

Example
Column A = 27/06/2009
Column B = 15/05/2009
Column C = John
Return John in column D if Column A is > 31/05/2009 and column B is <
31/05/2009.

Any ideas please?
 
Return John in column D if Column A is > 31/05/2009 and column B is <
31/05/2009.
Click to expand...
Try in D1 (assuming your data is in Row 1)

=IF(AND(A1>DATE(2009,5,31),B1<DATE(2009,5,31)),C1,"")
 
I want return the person's name [in] Column D [if] column
A is blank or greater than a certain date and if column B
is less than a certain date
Click to expand...

Enter your date boundaries in some cells:

E1 = 27/06/2009
F1 = 15/05/2009

Then use this array formula**

=INDEX(C2:C20,MATCH(1,((A2:A20="")+(A2:A20>E1))*(B2:B20<F1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Hi,

You can also try this:

=if(and(or(A1<>"",A1>=T1),B1<=U1),C1,"") where T1 and U1 hold 31/05/2009 and
31/05/2009 respectively.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top