IF statement comparing month and year separately

[Guest]

I have two cells that contain dates, C4 and G1. I need help writing an IF
statement that says

IF the date in G1 is >= C4+60 months AND if the month in C4 and G1 are the
same, then multiple by 1%. Otherwise do nothing.

If possible I need this in a single IF function because it will be nested in
a larger statement and I’m running into Excel’s limit of seven.

Can anyone help me out with the syntax here?

Thanks in advance.

spence

 

[Guest]

Try this:

=IF(AND(G1>=DATE(YEAR(C4),MONTH(C4)+60,DAY(C4)),MONTH(G1)=MONTH(C4)),TRUE,FALSE)

HTH,
Elkar

 

[Ron Rosenfeld]

I have two cells that contain dates, C4 and G1. I need help writing an IF
statement that says

IF the date in G1 is >= C4+60 months AND if the month in C4 and G1 are the
same, then multiple by 1%. Otherwise do nothing.

If possible I need this in a single IF function because it will be nested in
a larger statement and I’m running into Excel’s limit of seven.

Can anyone help me out with the syntax here?

Thanks in advance.

spence

With the Analysis ToolPak installed:

=Cell_Ref * if(and(g1>=edate(c4,60),month(c4)=month(g1)),1%,1)

where Cell_Ref is the value you wish to possibly multiply by 1%.
--ron

 

[George Nicholson]

In a single If():
=IF((DATE(YEAR(C4),MONTH(C4)+60,DAY(C4))<G1)*(MONTH(G1)=MONTH(C4))=1,"Multiply
by .01","Do nothing")

This relies on the fact that True = 1 (false = 0). so;
If 60MonthTest x SameMonthTest = 1, both Tests must be true.

HTH,

 

[Guest]

Thanks for the suggestion. It works perfectly when I use it by itself but
when I try to incorporate it into my existing formula (see below) I get a
value of TRUE instead of the calculation I need.

What I'm trying to do here is set up five year budget by month (months
across row 1) where the value of each cell is based on the previous month's
value. (Starting monthly salary is in cell E4.) The values need to increase
by 2.5% on the anniversary date in years 1 and two, by 3% in years three
through five, and by 1% every anniversary thereafter. So:

=IF(G$1=DATE(YEAR($C4),MONTH($C4)+60,DAY($C4)),(E4*1.03),
IF(G$1=DATE(YEAR($C4),MONTH($C4)+48,DAY($C4)), (E4*1.03),
IF(G$1=DATE(YEAR($C4),MONTH($C4)+36,DAY($C4)), (E4*1.03),
IF(G$1=DATE(YEAR($C4),MONTH($C4)+24,DAY($C4)), (E4*1.025),
IF(G$1=DATE(YEAR($C4),MONTH($C4)+12,DAY($C4)), (E4*1.025),E4)))))

When I nest your formula in either at the beginning or end I end up TRUE
values in my cells. Any advice on how to correctly incorporate it into my
existing formula? (I'm also open to starting from scratch if you have
anything better up your sleeve.)

Thanks again.

spence

 

[Guest]

Ron,

Thanks for you help. Your formula does exactly what I need it to do when
it’s by itself. But when I add it to the beginning or end of my existing
formula (see below) it doesn’t calculate but rather gives me a TRUE response.
It works perfectly when I use it by itself but when I try to incorporate it
into my existing formula I get a value of TRUE instead of the calculation I
need.

What I'm trying to do here is set up five year budget by month (months
across row 1) where the value of each cell is based on the previous month's
value. (Starting monthly salary is in cell E4.) The values need to increase
by 2.5% on the anniversary date in years one and two, by 3% in years three
through five, and by 1% every anniversary thereafter. So:

=IF(G$1=DATE(YEAR($C4),MONTH($C4)+60,DAY($C4)),(E4*1.03),
IF(G$1=DATE(YEAR($C4),MONTH($C4)+48,DAY($C4)), (E4*1.03),
IF(G$1=DATE(YEAR($C4),MONTH($C4)+36,DAY($C4)), (E4*1.03),
IF(G$1=DATE(YEAR($C4),MONTH($C4)+24,DAY($C4)), (E4*1.025),
IF(G$1=DATE(YEAR($C4),MONTH($C4)+12,DAY($C4)), (E4*1.025),E4)))))

This formula does everything except deal with the 1% annual increases after
the fifth year. Your formula deal with those perfectly. But I can seem to
figure out how to wed them. Any suggestions or advice? I’m also completely
open to rewriting this from scratch if you know of a superior way to do it.

Thanks again,
spence

 

[George Nicholson]

Create a two-column table named SalaryIncrements:
Months / Factor
0 / 1
12 / 1.025
36 / 1.03

Then calculate the # of months between your dates and use Vlookup to get
your increment factor:

= Vlookup(((Year(G1)-Year(C4)) * 12) + (Month(G1)-Month(C4)),
SalaryIncrements, 2) * E4


HTH,