IF(AND

  • Thread starter Thread starter Wolfwalker721
  • Start date Start date
W

Wolfwalker721

Hi =)
I have a colum of dates. In another spot on the worksheet I have 2 cells, 1
that sets the start date and 1 that sets the end date (usualy todays date). I
am trying to use a formula to

1)If the cell above is blank OR the date returned would not be within the
specified date range, I would like it to return "" (nothing)

2) If the cell above does indeed have a date AND that date falls in the date
ranger specified then return this (DATE(YEAR(A2),MONTH(A2)-$G$31,DAY(A2))))
with A2 being the cell right above this formula.


=IF(AND(A2="",((DATE(YEAR(A2),MONTH(A2)-$G$31,DAY(A2)))<F1)),"",(DATE(YEAR(A2),MONTH(A2)-$G$31,DAY(A2))))

A2 is the cell above
F1 Sets the start date
G31 set the number of months to subtract

I have no problem with using a different type of formula if there is a
better way to do this I am all for it.

Thanks in advance
 
I can read this two ways, Please test these formulas. Note that D1 holds
start date and D2 end date

A) 'the date return' is the date in cell A3
=IF(AND(NOT(ISBLANK(A2)),A3>=$D$1,A3<=$D$2),DATE(YEAR(A2),MONTH(A2)-$G$31,DAY(A2)),"")


B) 'the date returned' refers to your formula
DATE(YEAR(A2),MONTH(A2)-$G$31,DAY(A2)))
=IF(ISBLANK(A3),"",IF(AND(DATE(YEAR(A3),MONTH(A3)-$G$31,DAY(A3))
 
Hi

I think this is what you need:

=IF(A2<>"",IF(DATE(YEAR(A2),MONTH(A2)-$G$31,DAY(A2))<F1,(DATE(YEAR(A2),MONTH(A2)-$G$31,DAY(A2))),""),"")

Regards,
Per
 
THANKS!!!!! This works great!

Per Jessen said:
Hi

I think this is what you need:

=IF(A2<>"",IF(DATE(YEAR(A2),MONTH(A2)-$G$31,DAY(A2))<F1,(DATE(YEAR(A2),MONTH(A2)-$G$31,DAY(A2))),""),"")

Regards,
Per
Click to expand...
 
Back
Top