Another leap year is coming! Normally, I am a fan of anything that puts another day between now and my birthday. But recently, I discovered the chaos that a leap year – really, leap day – can cause in date calculations.

If you’ve ever cloned records where you have to move the date fields out one year, then you’ve done things like this:

Not an issue if you are manually cloning, right? Because you can simply adjust as needed for those pesky little February 29th dates.

I’ve created a flow that runs when someone clicks a button (on an opportunity page layout) named “Create Renewal Opp.” The flow creates a renewal opportunity, and also clones the products from the original opportunity. (I would also like to add that I could not have done it without this blog post – thanks Rakesh!) In this flow, there are some nifty date formulas that were working just fine… until we got closer to February 29th, 2016.

There were two ways that I attempted to calculate my dates + 1 year. The easiest was DateField + 365. It simply adds 365 days to the date it’s looking at. The problem with this is, if 2/29/2016 is within that 365 days, and what you really wanted was for the month and day to be the same (and just move the year out by one), then it won’t work. If you add 365 days to 7/15/2015, you will get 7/14/2016.

The other formula I tried was DATE (YEAR (DateField) +1, MONTH (DateField), DAY (DateField)). This keeps your month and day the same, and adds +1 to the year. This is perfect. Except… when it tries to add a year to 2/29/2016, it will fail – because 2/29/2017 is not a valid date.

So when I used my fancy “Create Renewal Opp” button on an opportunity that had 2/29/2016 as one of the dates, I got a flow error message, as well as an email alert that told me I had an invalid date value.

No renewal opportunity was created.

There are some really complicated formulas out there in the Help & Training documentation that are supposed to get around this leap year issue. But really, if you are just adding one year (or two years, or whatever), it is much simpler than that. After extensive testing and trial & error, I finally settled on a formula that worked for me:

To break it down: if the date is February 29th, then add 365 days (which will take you to February 28th of the next year) – otherwise, add 1 to the year and leave the month and day the same. The beauty of this calculation is that it never looks for a specific year. It just keeps things in line during any year, leap or not.

So keep this handy for when you need an “add-a-year” calculation, enjoy the fact that you’ll never again need to re-calculate things because of leap years, and relax!