Excel IF(OR) Formula for a Future Date
I have a list of dates and I need a formula that calculates the following:
If the month in [DATE] is October, November, or December, then the result is November 1 of 6 years from the year in [DATE]; if not, result is November 1 of 5 years from the year in [DATE]
This is my current formula:
=IF(OR(MONTH(G2)=MONTH(10),MONTH(11),MONTH(12)),(DATE(YEAR(G2)+6,11,1)),(DATE(YEAR(G2)+5,11,1)))
The problem is this:
G2 = 11/19/2014 and the result is 11/1/2020...which is correct.
BUT if I use the formula on the next line:
H2 = 5/16/2016 and the result is 11/1/2022...which is incorrect.
I have fiddled with this for a long while now, changing the formula, etc., and either I get an error or it gives the incorrect date result.
Please help!
2 Answers
The Month() function returns an Integer. If you use MONTH(10) that returns a 1 for January since the number 10 will translate to the 10th January 1900, not the month of October.
When using OR() you need to write out all conditions in full. You cannot just string them along separated by commas.
The way you have written the OR condition, it will always return true, because Month(11) = 1 and 1 equals to TRUE. That alone is enough to make the whole OR function return TRUE.
Tip for troubleshooting a formula like this: Use the Evaluate Formula command on the Formulas ribbon and step through the formula. You can see what each element resolves to and you can then take corrective action.
And finally, don't use more brackets than needed. It just complicates the formula.
Your corrected formula is:
=IF(OR(MONTH(G2)=10,MONTH(G2)=11,MONTH(G2)=12),DATE(YEAR(G2)+6,11,1),DATE(YEAR(G2)+5,11,1))Of course, you only need to check if the month is later than September, so your formula can be shortened to
=IF(MONTH(G2)>9,DATE(YEAR(G2)+6,11,1),DATE(YEAR(G2)+5,11,1))And to make it a bit more efficient, you can move the IF condition into the argument that gets added to the year.
=Date(YEAR(G2)+IF(MONTH(G2)>9,6,5),11,1)Since your criteria are only October, November & December, therefore I would like to suggest two possible methods, works with IF and OR, also returns Error if month is not between 1 and 12.
Method 1:
=IFERROR(IF(OR(MONTH(G2)={10,11,12}),DATE(YEAR(G2)+6,11,1),DATE(YEAR(G2)+5,11,1)),"Wrong month only 1-12")Method 2:
=IFERROR(DATE(YEAR(G2)+IF(OR(MONTH(G2)={10,11,12}),6,5),11,1),"Wrong month only (1-12)")Note:
- Date format is
MM/DD/YY. IF(OR(MONTH(G2)={10,11,12})checks whether month is either October, November or December.IFERRORreturns error message, in case the Month is not between 1 and 12.
