Excel IF 1,2 and 3 months back

[dntel123]

Hiya guys,

I'm trying to write an IF statement in Excel 2010.

What I am doing is:

IF [Cell with Date in it]
is in the last month then put an 'a' in the current cell
IF the same cell is 2 months ago then put a 'b'
IF the same cell is 3 months ago then put a 'c'
Otherwise put a 'd'

At the moment I have 3 columns somewhere that have the last 3 months, 1 in each column. And my IF statement reads off the dates I am looking at against those, but I'm looking for a more accurate way of doing this. This is my current statement:

=IF(D2>=H2,"a",IF(D2>=I2,"b",IF(D2>=J2,"c","d")))

If anyone has a way I can streamline this, I'm mainly thinking of speed, I'd be grateful.

 

[chergh]

This isn't the easiest but this should work, the formula assumes the date for comparison is in cell A1:

=IF((YEAR(NOW())-YEAR(A1))*12+MONTH(NOW())-MONTH(A1) = 1,"A",IF((YEAR(NOW())-YEAR(A1))*12+MONTH(NOW())-MONTH(A1)=2,"B",IF((YEAR(NOW())-YEAR(A1))*12+MONTH(NOW())-MONTH(A1)=3,"C",IF((YEAR(NOW())-YEAR(A1))*12+MONTH(NOW())-MONTH(A1)>=4,"D","SAME MONTH"))))

 

[mfd1979]

I think this is what you are looking for assuming cell D2 is the referenced date subtracted from today's date:

=IF(MONTH(D2)=MONTH(TODAY())-1,"A",IF(MONTH(D2)=MONTH(TODAY())-2,"B",IF(MONTH(D2)=MONTH(TODAY())-3,"C","D")))

Cheers,

mfd1979

 

[chergh]

I think this is what you are looking for assuming cell D2 is the referenced date subtracted from today's date:

=IF(MONTH(D2)=MONTH(TODAY())-1,"A",IF(MONTH(D2)=MONTH(TODAY())-2,"B",IF(MONTH(D2)=MONTH(TODAY())-3,"C","D")))

Cheers,

mfd1979

That won't work for some months, such as January.

 

[mfd1979]

He is looking for 3 months back, the year is irrelavent. My formula is only looking at the month...if it is January today, December would be A, November would be B, October would be C, else would be D?

No?

 

[mfd1979]

considering the year...

=IF(DATE(YEAR(D2),MONTH(D2),)=DATE(YEAR(TODAY()),MONTH(TODAY())-1,),"A",IF(DATE(YEAR(D2),MONTH(D2),)=DATE(YEAR(TODAY()),MONTH(TODAY())-2,),"B",IF(DATE(YEAR(D2),MONTH(D2),)=DATE(YEAR(TODAY()),MONTH(TODAY())-3,),"C","D")))

mfd1979

 

[namliam]

Not perfect but close:

IF(MONTH(TODAY()-A1)<4
  ,CHAR(CODE("A")+MONTH(TODAY()-A1))
  ,"D")

Another option is to use:

IF((YEAR(TODAY())*12+MONTH(TODAY()))-(YEAR(A1)*12+MONTH(A1))<4
  ,CHAR(CODE("A")+(YEAR(TODAY())*12+MONTH(TODAY()))-(YEAR(A1)*12+MONTH(A1)))
  ,"D")

(YEAR(TODAY())*12+MONTH(TODAY()))-(YEAR(A1)*12+MONTH(A1))

 

[chergh]

He is looking for 3 months back, the year is irrelavent. My formula is only looking at the month...if it is January today, December would be A, November would be B, October would be C, else would be D?

No?

Your missing the point. The month function returns an integer between 1 and 12 so when the month in January the month function returns 1 and if you subtract one from that the result is 0 not 12.

Your second function works but I would still include the condition for "D" which allows the final false condition to identify the current month, and it's not any simpler than my formula.

 

[chergh]

Not perfect but close:

IF(MONTH(TODAY()-A1)<4
  ,CHAR(CODE("A")+MONTH(TODAY()-A1))
  ,"D")

Another option is to use:

IF((YEAR(TODAY())*12+MONTH(TODAY()))-(YEAR(A1)*12+MONTH(A1))<4
  ,CHAR(CODE("A")+(YEAR(TODAY())*12+MONTH(TODAY()))-(YEAR(A1)*12+MONTH(A1)))
  ,"D")

(YEAR(TODAY())*12+MONTH(TODAY()))-(YEAR(A1)*12+MONTH(A1))

Nice stuff, hadn't thought of doing it like that but you need to subtract 1 from the char code so it returns the correct number.

IF((YEAR(TODAY())*12+MONTH(TODAY()))-(YEAR(A1)*12+MONTH(A1))<4
  ,CHAR(CODE("A")-1+(YEAR(TODAY())*12+MONTH(TODAY()))-(YEAR(A1)*12+MONTH(A1)))
  ,"D")

I really like this method.

 

[namliam]

Yeah, ok, the -1...

I had: Char(63 +(YEAR(TODAY())*12+MONTH(TODAY()))-(YEAR(A1)*12+MONTH(A1)))
but wanted to do the Code("A") to show where / what the 63 is... which obviously is 64 not 63.

I seem to have a nack for working with stupid date things like this, all kinds of people come to me asking for this shyte with dates.