Learnsmarter -Salesforce formula to add a month to a date (or today)
Salesforce formula to add a month to a date (or today)
Published 11th March 2017
By Sean Dukes
I was doing a Trailhead assignment and I needed a formula to add one month to a date. I did a quick bit of research on the web and I really struggled to find the answer. There were a lot of complicated half answers that didn't make sense to me, some that were just an approximation (add 30 days) and some that didn't work on every day of the month. I wondered if there was a simpler way.

Subsequently, I did find a great answer here: https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm and if I'd found that sooner I probably wouldn't have come up with my own solution. 


The problem of course is fairly obvious. Firstly, there’s no standard function in Salesforce formulas that does that, secondly not every month has the same number of days and finally you have to think about leap years too. The formulas that take this account look at the structure of leap years, which is every four years starting at zero; so year 4 is a leap year, so is 2004, 2016 and 2020. It's actually a tiny bit more complicated than that, because if a year can be divided by 100 but not 400 it's not a leap year, so 2100 is not a leap year. For most practical purposes, you can probably ignore that. Interestingly, this every hundred years complication didn't exist before 1582 when 11 days were lost in one fell swoop (try putting that in your formula!), however the use of leap years has existed since at least 45BC. Anyway, you can work out whether the year is a leap year by testing to see if it’s a multiple of four and you can work out how many days you need based on the month you’re starting with and so on. Yes. It’s possible. But it takes a bit of understanding.

I started looking at this from a different angle. So seven months of the year have 31 days. You could try just adding 31 days every time and quite often you’re going to get the right answer. This got me thinking. What about a formula that adds 31 days and checks if that is the right answer? If the answer is correct the day of the month will be the same and we can test for this using the DAY function. The theory goes like this …

“If the day of the month of the date you start with is the same as the day of the month you end with when you add 31 days, then 31 days is the right number of days to add.”

You can put that in a function easily.

IF(DAY([Date_Value])=DAY([Date_Value]+31), Add 31 days, Try something else)

You can then add a second function nested within the first that trys 30 days.

IF(DAY([Date_Value])=DAY([Date_Value]+31), Add 31 days,
IF(DAY([Date_Value])=DAY([Date_Value]+30), Add 30 days, Try something else )
)

That will actually work perfectly for much of the year but it’s no good in February and it’s no good when the date you start with is 31st and the following month has 30 days or less.

February is pretty easy and so are leap years as it turns out. You just keep going a couple more times.

IF(DAY([Date_Value])=DAY([Date_Value]+31), Add 31 days,
IF(DAY([Date_Value])=DAY([Date_Value]+30), Add 30 days,
IF(DAY([Date_Value])=DAY([Date_Value]+29), Add 29 days,
IF(DAY([Date_Value])=DAY([Date_Value]+28), Add 28 days, Try something else )
)))
 
That only leaves about 10 dates that this doesn’t work on, so now we just need to deal with them.

These are all going to be dates where the month ends in 31 and the next month is shorter. In this case, DAY([Date_Value]+31) is either going to return 1,2 or 3. In any of those cases, you just deduct the result from 31 and you’ll have the last day of the month. Put that all together and the formula looks like this.

IF(DAY([Date_Value]+31)=DAY([Date_Value]),[Date_Value]+31,
IF(DAY([Date_Value]+30)=DAY([Date_Value]),[Date_Value]+30,
IF(DAY([Date_Value]+29)=DAY([Date_Value]),[Date_Value]+29,
IF(DAY([Date_Value]+28)=DAY([Date_Value]),[Date_Value]+28,
IF(DAY([Date_Value]+31)=1,[Date_Value]+30,
IF(DAY([Date_Value]+31)=2,[Date_Value]+29,
[Date_Value]+28
))))))

And it works a treat regardless of whether it’s a leap year or not :)

If you just want to get a date one month from today, this works for me.

IF(DAY(TODAY()+31)=DAY(TODAY()),TODAY()+31,
IF(DAY(TODAY()+30)=DAY(TODAY()),TODAY()+30,
IF(DAY(TODAY()+29)=DAY(TODAY()),TODAY()+29,
IF(DAY(TODAY()+28)=DAY(TODAY()),TODAY()+28,
IF(DAY(TODAY()+31)=1,TODAY()+30,
IF(DAY(TODAY()+31)=2,TODAY()+29,
TODAY()+28
))))))
 
The downside of this code is that it doesn’t work well if you want to add a variable number of months. If you want to add a set number of months and it’s more than one, then you can use the same principles, but you may have more potential answers to test. If you want to add a year, you just add 365 days and if the day of the month is not the same, then you need to add 366 and if that doesn't work, you must be starting on 29th February and you need to add 364 days so that the next date is 28 February (although opinions are divided here; some people think that one year from February 29th should be March 1st - up to you).

Please try it out and feel free to leave any comments you may have.
 
comments powered by Disqus
Categories
Follow us on Twitter
Like us on Facebook