IF statement comparing month and year separately
 

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 Im running into Excels limit of seven.

Can anyone help me out with the syntax here?

Thanks in advance.

spence

IF statement comparing month and year separately
 

Try this:

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

HTH,
Elkar


"spence" wrote:

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 Im running into Excels limit of seven.

Can anyone help me out with the syntax here?

Thanks in advance.

spence

IF statement comparing month and year separately
 

On Thu, 26 Apr 2007 10:10:05 -0700, spence
wrote:

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

IF statement comparing month and year separately
 

In a single If():
=IF((DATE(YEAR(C4),MONTH(C4)+60,DAY(C4))<G1)*(MONT H(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,


"spence" wrote in message
...
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

IF statement comparing month and year separately
 

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

"Elkar" wrote:

Try this:

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

HTH,
Elkar


"spence" wrote:

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 Im running into Excels limit of seven.

Can anyone help me out with the syntax here?

Thanks in advance.

spence

IF statement comparing month and year separately
 

Ron,

Thanks for you help. Your formula does exactly what I need it to do when
its by itself. But when I add it to the beginning or end of my existing
formula (see below) it doesnt 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? Im also completely
open to rewriting this from scratch if you know of a superior way to do it.

Thanks again,
spence


"Ron Rosenfeld" wrote:

On Thu, 26 Apr 2007 10:10:05 -0700, spence
wrote:

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 Im running into Excels 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

IF statement comparing month and year separately
 

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,


"spence" wrote in message
...
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

"Elkar" wrote:

Try this:

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

HTH,
Elkar


"spence" wrote:

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