Excel Formula Help - Multiple IF/AND Statement with Dates

[Johnny Thunder]

Hello All,

I am in need of some help with a hard IF/AND Statement with multiple variables

My current Formula:

=IF(T$4=$J9-1,$Q9*0.3,IF(T$4=$J9,$Q9*0.5,IF(T$4=$J9+1,$Q9*0.1,"")))

Definitions
T$4 = is my header date (Formula is dragged left to right in the same row
$J9 = My Starting Date, all the subsequent uses of J9's date have a plus 1 or negative one. So if my J9's start date is Nov-19 and I use -1 I would expect the formula to see Oct-19.
$Q9 = $Amount
*0.3 or 30% = Is the variable I would like to use for -1
*0.5 or 50% = Is the variable I would like to use for StartDate
*0.1 or 10% = Is the variable I would like to use for +1

I thought the formula I wrote would work but no dice. Any ideas would help and unfortunately I can't use VBA in this case.Also, a non-array formula would be best for the users I am writing this workbook for.

Thanks in advance.

 

[jtakw]

Hi,

If J9 is a Real Date value, adding or substracting 1 is Only adding 1 day or minus 1 day.

Replace J9+1 with EDATE(J9,1)

and J9-1 with EDATE(J9,-1)

Didn't go thru your formula, just pointing out the Date error.

 

[Johnny Thunder]

Thanks! @jtakw that pointed me in the right direction. My formula was so close, just needed that EDATE piece: =IFERROR(IF(T$4=EDATE($J9,-1),$Q9*MRNeg1,IF(T$4=$J9,$Q9*MRStart,IF(T$4=EDATE($J9,1),$Q9*MRPlus1,IF(T$4=EDATE($J9,2),$Q9*MRPlus2,"")))),"")

Works great!

 

[jtakw]

You're welcome.