Comparing dates in IF statement

Question

In an IF statement I want to check if date in A1 falls in the month immediately prior to the date in B1. I have tried various ways but none of them seem to work.

Can you help please.

Answer 1

Thank you Hans. I nearly had this right, just overlooked to do double quotes on the third part of the IF statement.

But without your help I would never have worked out the formula you suggested to my original post; I was not aware of EDATE function.

Answer 2

You have to double the quotes if you want to include a quoted string inside another quotes string, otherwise VBA thinks the string ends prematurely:

ActiveCell.FormulaR1C1 = "=IF(TEXT(EDATE(E1,0),""yymm"")<TEXT(RC[-5],""yymm""),RC[-1],"""")"

Answer 3

Thank you very much for your suggestions; they work just fine on the spreadsheet.

But I have encountered problems when using it in vba. I get Compile Error: Expected End of Statement error when trying this:

ActiveCell.FormulaR1C1 = "=IF(TEXT(EDATE(E1,0),"yymm")<TEXT(RC[-5],"yymm"),RC[-1],"")"

Debug highlights "yymm" so I guess use of quote marks in the statement are incorrect. I tried ""yymm"" and removing them but still get errors.

Would very much appreciate your help in getting this right.

Thank you.

Answer 4

=TEXT(EDATE(B1,-1),"yymm")=TEXT(A1,"yymm")

or

=IF(TEXT(EDATE(B1,-1),"yymm")=TEXT(A1,"yymm"), "in prior month", "not in prior month")