Salesforce Date Formula – Today’s date plus one year

We often get requests to calculate a date that’s one year, or sometimes one year minus one day from a date like an Opportunity’s Close Date. For example, if an Opportunity includes Products with a license that is valid for just one year, the license will expire on a date that’s one year minus one day from the Opportunity’s Close Date. Creating a Salesforce date formula that calculates the expiration date allows us to run reports to see which Opportunities are about to expire. Now, you’d think calculating an expiration date would be easy, but it turns out to be just a little harder than you might expect.

First, a few tips.

Tip One:

In a formula, if you want to add a certain number of days to a given date, just use the plus operator. For example, DATE(CloseDate)+1 gives you a date that’s one day after the Close Date.

Tip Two:

You can use the MONTH(), DAY(), and YEAR() formula functions to get the various parts of a date. For example, MONTH(CloseDate) gives you the month of the CloseDate, as a number. (JavaScript programmers beware: January is month 1 in Salesforce, not month 0 like it is in JavaScript.)

Tip Three:

You can use the DATE() formula function to create a date value. For example, DATE(2012, 1, 31) returns the date January 31, 2012.

Now, back to our challenge, calculating the expiration date of an Opportunity based on its Close Date.

The first mistake most people make is to think that they can just add 364 days to the Close Date. After all, if “1 year” is 365 days, then “one year minus one day” should be 364 days, right? So they wind up with a formula field like this:

     DATE(CloseDate) + 364

That works most of the time, but what about leap years? If the Close Date is 1/1/2012, and you add 364 days, you wind up with 12/30/2012, when what you want is 12/31/2012. Why? Leap years have 366 days, so adding 364 days to a date that involves a leap year leaves you one day off.

The next mistake most people make is to think they can add 1 year to the Close Date, then subtract 1 day from that. They wind up with a formula like this:

     DATE(YEAR(CloseDate)+1, MONTH(CloseDate), DAY(CloseDate)) – 1

What’s wrong with this? After all, if Close Date is 1/1/2012, your formula calculates

     DATE(2012+1, 1, 1) – 1

The end result is 1/1/2013 minus 1 day, or 12/31/2012, and surely that’s correct. Isn’t it?

Well, what if the Close Date is 2/29/2012? Since 2012 is a leap year, that’s a perfectly valid date. The formula calculates

     DATE(2012+1, 2, 29) – 1

The first part of the Salesforce date formula tries to create a date of 2/29/2013, and since that’s not a valid date, you’ll get an error before the formula gets around to subtracting 1 from that date.

In order to calculate the expiration date, you have to explicitly handle the leap year case. Here’s a formula that works:

     IF(MONTH(CloseDate)=2 && DAY(CloseDate)=29,
        DATE(YEAR(CloseDate)+1, 2, 28),
        DATE(YEAR(CloseDate)+1, MONTH(CloseDate), DAY(CloseDate)) – 1
     )

In this formula, we explicitly check whether the Close Date is February 29, regardless of the year. If it is, the formula returns February 28 of the following year; otherwise, the formula adds 1 to the Close Date’s year to calculate the date that’s exactly 1 year after the Close Date, then subtracts 1 day from that.

Tinkering with this Salesforce date formula, there’s one more change I’d make. The Opportunity CloseDate field is always required to have a value, but what if you’re working with a date field that’s not required, like Contact.Birthdate? If you use MONTH() or other functions for a date field that has no value, you’ll get an error.  To protect against that, make sure your formula has “Treat blank fields as blanks” selected, then change the formula to

IF(ISBLANK(Birthdate),
Birthdate,
IF(MONTH(Birthdate)=2 && DAY(Birthdate)=29,
DATE(YEAR(Birthdate)+1, 2, 28),
DATE(YEAR(Birthdate)+1, MONTH(Birthdate), DAY(Birthdate)) – 1
)
)

If the Birthdate is blank, the formula returns a blank date; otherwise, it returns the day before the Contact’s Birthdate in the next year.

Do you still feel confused on how to use Salesforce date formulas?

We get it, Salesforce date formulas aren’t always easy to build out in your system. Learn more about how to handle your toughest Salesforce Platform roadblocks with other content on our Learning Center. If you think you fall into that category, let our team help! Work with Salesforce certified experts who can advise on what your organization needs. Connect with a member of our team to discuss your project, then access a deep bench of Salesforce consultants to meet your needs. Are you ready to reach for operational excellence?