I have an existing formula field that is displaying an error on the field (#Error!) when the month is June(6). Would someone be able to help in how I should re-write this formula?

IF(MONTH( Date Field ) >= 6,
    DATE(YEAR( Date Field ) + 5, MONTH( Date Field ) - 6, DAY( Date Field )),
    DATE(YEAR( Date Field ) + 4, MONTH( Date Field ) + 6, DAY( Date Field ))
  • What is the error message you're getting? Please add it to your question (without paraphrasing) by making an edit. – Derek F Sep 20 '19 at 13:52
  • I have updated my comment, I am just getting (#Error!) on that field – Chelsea B Sep 20 '19 at 13:57
  • You're getting #Error because MONTH( Date Field ) - 6 is equal to zero. If you're trying to add 4.5 years, why don't you use the ADDMONTHS function to add 54 months? – Chris Johnson Sep 20 '19 at 14:17
  • Thank you, Chris – Chelsea B Sep 23 '19 at 19:39

When your month evaluates to 6, you're trying to create a date with a month value of 0 (which is not valid).

This formula can also have issues if the date you're adding 4.5 years to is on the 29th, 30th, or 31st of August (which would put you at February 30th, for example)...or several other dates (like October 31st).

Probably the easiest/most reliable way you can do this with a formula is just to add the number of days to the date. Adding over 4 years means you will encounter a leap day (except for years like 2100 which are divisible by 4, but not 400), so the number of days you want to add is (365 * 4) + 1 + (365 / 2) = 1643.5. Round to whatever integer you see fit.

Your formula would end up being <Date field api name> + 1644

| improve this answer | |
  • 1
    Thank you for your help, Derek! – Chelsea B Sep 20 '19 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.