In this Salesforce tutorial, we will the different Date Formula Functions in Salesforce. Moreover, we will go through some of the common use date function formulas in Salesforce Lightning and Salesforce Classic.
While working in an organization that uses Salesforce as a CRM tool, I got a task to build an application where I need to store and fetch dates. To fulfill this requirement, I use the various date formula functions of Salesforce.
So, If you want to learn about Date Formula Functions in Salesforce and their steps to use in Salesforce Lightning and Salesforce Classic edition, you have come to the right place.
The following are the topics that we will cover in this salesforce tutorial:
- Salesforce Advance Formula Function
- Salesforce Date Formula Function
- Applications of Date Formula Functions in Salesforce
- Common used Date Formula Functions in Salesforce
Salesforce Advance Formula Function
The advance formula function is the pre-built function in Salesforce, that we will use to do calculations, work with date, time, and data, and also to convert text.
We can use these formula functions in Salesforce workflow rules, formula fields, process builders, validation rules, etc. The Salesforce advance formula functions include logical, mathematical, text, date, and time functions.
With this, we have learned about Salesforce Advance Formula Function. Now, we will proceed and learn about Salesforce Date Function Formula.
Salesforce Date Formula Function
As we mentioned above, Salesforce Advance Formula Functions include Date and Time Functions. So, we will now talk about the Salesforce Date Formula Function in detail.
The Date formula function is the type of advance formula function in Salesforce. It is used to work with dates and times.
These functions are used to format dates and times, extract data, calculate gaps between dates, and conduct other actions on dates and times.
This has taught us about the Salesforce Date Formula Function. Now, we’ll continue and learn about Salesforce Date Function Formula applications.
Applications of Date Formula Functions in Salesforce
Here, are some applications of Salesforce Date Formula Functions:
- The date formula function is used to calculate the age of an account holder.
- We can use the date formula function to calculate how many years your account is old.
- The date functions can be used to arrange follow-up actions for a certain day, such as scheduling a phone call, campaign, task, lead check, etc.
- We can use the date formula function to calculate the number of days between specific date range records.
- The date functions can be used to determine the duration of an event, and also the last date.
- To calculate the expiry date of contracts, licenses, certifications, and products, we use the Salesforce date functions.
- To build custom date and time fields that fit our unique business needs, we utilize the date function formulas.
As a result, we now know some common use cases of Salesforce Date formula functions. Next, we’ll look at various commonly used date formula functions in Salesforce with examples.
Common used Date Formula Functions in Salesforce
There are a number of date functions available in formula fields in Salesforce. Some of the most popular ones are highlighted below:
- TODAY(): This function is used to return the present date.
- NOW(): This function returns the time and date as of the moment.
- DATE(): This function creates and returns a date that matches the given year, month, and day.
- YEAR(): This function returns the corresponding year.
- MONTH(): This function returns a month as a number for a given date.
- DAY(): This function returns the day from the date in number format.
- WEEKDAY(): Displays the weekday as a number from the given date.
- ISOYEAR(): This function returns the ISO week year for the given date.
- ISOWEEK(): This function returns the ISO week number for the given date.
- DAYOFYEAR(): This function returns the day of the year from the given date.
- DATEVALU(): This function converts a date string into a date value.
- DATETIMEVALU(): This function transforms a DateTime string into a DateTime value.
- ADDMONTHS(): This function extends a date by a predetermined number of months.
Let’s discuss each of these functions in detail with syntax and examples.
Read: Add Days to Date in Salesforce Formula
Salesforce TODAY() function
TODAY() formula function in Salesforce, is used to return the current date only without any time value. It returns the date based on the user’s time zone setting in Salesforce.
The syntax of the function is as given below:
TODAY()- This function does not include any parameter, we only have to write the word TODAY in the formula.
Let’s see a simple example, of where we can use the TODAY function.
Suppose, we want to calculate the number of days between the current date and the birth date of the employee. So, for this, we will use the TODAY() function in the formula field.
Here, is the formula:
TODAY() - Birthday__c- In this example, we subtract the TODAY() function from the Birth Date custom date field called “Birthday__c”, which returns the number of days between the current date and the birthdate.
- This advance formula function, return result in a numeric value representing the number of days.
If we want to return the date value with time information, we need to use the NOW() function. So, let’s proceed and learn about the NOW function in detail.
Salesforce NOW() function
NOW() formula function in Salesforce, is used to return the current date and time value. It returns the date based on the user’s time zone setting in Salesforce.
This function works similarly to the TODAY() function. The only difference is this it returns the date value with time information also.
The syntax of the function is as given below:
NOW()- This function does not include any parameter, it is a simple function where we only have to write the word NOW in the formula.
Let’s see a simple example, of where we can use the NOW function.
Suppose, we want to calculate the time remaining and days left in the subscription expiry. So, for this, we will use the NOW() function in the formula field.
Here, is the formula:
IF(Expiry_Date_Time__c > NOW(), Expiry_Date_Time__c - NOW(), 0)- In this example, we will use the IF function, which checks whether the custom field expiry date and time called “Expiry_Date_Time__c” is greater than the current date and time called “NOW()”.
- If it is, the formula subtracts the current date and time from the expiry date and time and returns the time left until the due date and time.
- If not, the formula returns 0.
If we want to return and store the date value, we need to use the DATE() function. So, let’s proceed and learn about the DATE function in detail.
Salesforce DATE() function
DATE() formula function in Salesforce, is used to return the date data type that meets the given year, month, and day. It returns the date based on the user’s time zone setting in Salesforce.
This function also needs to create a date value based on a given year, month, and day.
The syntax of the function is as given below:
DATE(year, month, day)- This function includes three parameters, YYYY which represents the year in 4 digits, MM which represents the month in 2 digits, and DD which represents the day in 2 digits.
Let’s see a simple example, of where we can use the DATE() function.
DATE(2023, 04, 04)In this example, we use the DATE() function in which we pass the YYYY as 2023, MM as 04, and DD as 04 and it returns the date as April 4th, 2023.
If we want to fetch the year from the date value, we need to use the YEAR() function. So, let’s proceed and learn about the YEAR function in detail.
Salesforce YEAR() function
YEAR() formula function in Salesforce is used to return the year value of a given date. It returns the result in the number format.
The syntax of the function is as given below:
YEAR(date)- This function includes one parameter, i.e date which takes a date in YYYY-MM-DD format.
Let’s see a simple example, of where we can use the YEAR() function.
Suppose, we want to extract the year from the date on which the account was created. So, for this, we will use the YEAR() function in the formula field.
Here, is the formula:
YEAR(Date_Of_Joining__c)- In this example, we will return the year value from the date of joining called the “Date_Of_Joining__c” field.
If we want to fetch the month from the date value, we need to use the MONTH() function. So, let’s proceed and learn about the MONTH function in detail.
Salesforce MONTH() function
MONTH() formula function in Salesforce is used to return the month of a given date value. It returns the value in numbers between 1 to 12.
The syntax of the function is as given below:
MONTH(date)- This function includes one parameter, i.e date which takes a date in YYYY-MM-DD format.
Let’s see a simple example, of where we can use the MONTH() function.
Suppose, we want to extract the month from the Invoice. So, for this, we will use the MONTH() function in the formula field.
Here, is the formula:
MONTH(Invoice_Date__c)- In this example, we will return the month value from the Date of the Invoice called the “Invoice_Date__c” field.
- The function returns an integer value between 1 and 12 that represents the month of the date.
If we want to fetch the day from the date value, we need to use the DAY() function. So, let’s proceed and learn about the DAY function in detail.
Salesforce DAY() function
DAY() formula function in Salesforce is used to return the day of a given date value.
The syntax of the function is as given below:
DAY(date)- This function includes one parameter, i.e date which takes a date in YYYY-MM-DD format.
Let’s see a simple example, of where we can use the DAY() function.
Suppose, we want to extract the day from the date of lead was created. So, for this, we will use the DAY() function in the formula field.
Here, is the formula:
DAY(Lead_Start_Date__c)- In this example, we will use the DAY() function to get the day value from the date field called “Lead_Start_Date__c”. It will return the day value as a number.
- Let’s say we have Lead_Start_Date__c equal to 2023-03-29, the formula will return the day of the month on which the lead started as 23.
If we want to fetch the weekday from the date value, we need to use the WEEKDAY() function. So, let’s proceed and learn about the WEEKDAY function in detail.
Salesforce WEEKDAY() function
WEEKDAY() formula function in Salesforce is utilized to get the day of the week for a specific date. It returns the numbers between 1 to 7, where 1 denotes Sunday and 7 denotes Saturday.
The syntax of the function is as given below:
WEEKDAY(date)- This function takes one parameter, the date in YYYY-MM-DD format whose weekday we want to determine.
Let’s see a simple example, of where we can use the WEEKDAY() function.
Suppose, we want to find the day of the week for the Upcoming Campaign Event. So, for this, we will use the WEEKDAY() function in the formula field.
Here, is the formula:
WEEKDAY(Upcoming_Campaign_Event__c)- In this example, we will use the WEEKDAY() function that will display the day of the week on which the upcoming campaign event is, based on the value “Upcoming_Campaign_Event”.
- Let’s say we have an upcoming campaign event on a Thursday, so it will display the result as 4. ( Thursday is the fourth day of the week ).
If we want to fetch the iso year from the date value, we need to use the ISOYEAR() function. So, let’s proceed and learn about the ISOYEAR function in detail.
Salesforce ISOYEAR() function
ISOYEAR() formula function in Salesforce is used to return the ISO year for a given date. The ISO year is a standard year number used in the Gregorian calendar.
The syntax of the function is as given below:
ISOYEAR(date)- This function takes one parameter, the date in YYYY-MM-DD format whose ISO year we want to find.
Let’s see a simple example, of where we can use the ISOYEAR() function.
Suppose, we want to find the ISO year for the date. So, for this, we will use the ISOYEAR() function in the formula field.
Here, is the formula:
ISOYEAR(DATE(2023,04,02))- In this example, we want to find the ISO year for the given date “2023-04-02”, using the ISOYEAR function.
- This function will return the result in number as 2023.
If we want to find, ISO week we can easily find it using the ISOWEEK function. So, let’s learn it in detail.
Salesforce ISOWEEK() function
ISOWEEK() formula function in Salesforce is used to return the ISO week number for a given date. The ISO week is a standard week number that is the most commonly used week numbering system.
In the ISOWEEK numbering system, weeks start on Monday and end on Sunday.
The syntax of the function is as given below:
ISOWEEK(date)- This function takes one parameter, the date in YYYY-MM-DD format whose ISO week number we want to find.
Let’s see a simple example, of where we can use the ISOWEEK() function.
Suppose, we want to find the ISO week number of the given date. So, for this, we will use the ISOWEEK() function in the formula field.
Here, is the formula:
ISOWEEK(DATE(2023-04-05))- The result is returned in number format as 14. It means that the ISO week number for 5th April 2023 is number 14.
If we want to fetch the day of the year from the date value, we need to use the DAYOFYEAR() function. So, let’s proceed and learn about the DAYOFYEAR function in detail.
Salesforce DAYOFYEAR() function
DAYOFYEAR() formula function in Salesforce, is used to return the day of the year of the given date field. This function returns the value in number format between 1 and 366.
The syntax of the function is as given below:
DAYOFYEAR(date)- This function takes one parameter, the date in YYYY-MM-DD format whose day of the year we want to display.
Let’s see a simple example, of where we can use the DAYOFYEAR() function.
Suppose, we want to find on which day of the year the account of the client is created. So, for this, we will use the DAYOFYEAR() function in the formula field.
Here, is the formula:
DAYOFYEAR(Account_Create_Date__c)- Let’s say Account is created on April 5th 2023, so this formula will return a day of year value as 95. (April 5th is the 95th day of the year 2023).
If we want to convert the date from string to date datatype, we need to use the DATEVALUE() function. So, let’s proceed and learn about the DATEVALUE function in detail.
Salesforce DATEVALUE() function
DATEVALUE() formula function in salesforce is used to convert the date stored in a field in text or string datatype to date datatype. In simple words, it converts the date represented in the text string to the salesforce date value.
The syntax of the function is as given below:
DATEVALUE(datestring)- This function takes one parameter, the date in “YYYY-MM-DD”, “MM/DD/YYY”, or “DD/MM/YYYY” format in the string format.
- Make sure the date is enclosed in quotes.
Let’s see a simple example, of where we can use the DATEVALE() function.
Suppose, we have stored the date of the lead started in the string format and now we want to convert it into a date value. So, for this, we will use the DATEVALUE() function.
Here, is the formula:
DATEVALUE("05/04/2023")- This formula will return a date value as 2023-04-05.
If we want to convert the datetime value from string to date datatype, we need to use the DATETIMEVALUE() function. So, let’s proceed and learn about the DATETIMEVALUE function in detail.
Salesforce DATETIMEVALUE() function
DATETIMEVALUE() formula function in Salesforce, is used to convert the date and time stored in a field in text or string datatype to date datatype.
In simple words, it converts the date and time represented in the text string to the salesforce date value.
The syntax of the function is as given below:
DATETIMEVALUE(datetimestring)- This function takes one parameter, the date in “YYYY-MM-DDThh:mm:ssZ” format, where
- YYYY represents the year, MM represents the month, DD represents the day,
- T represents the time separator, hh represents the hours, mm represents the minutes, ss represents the seconds, and Z represents the time zone.
- Make sure the date and time are enclosed in quotes.
Let’s see a simple example, of where we can use the DATETIMEVALE() function.
Suppose, we have stored the date and time of the employee coming to the office in the string format and now we want to convert it into a date and time value. So, for this, we will use the DATETIMEVALUE() function.
Here, is the formula:
DATETIMEVALUE(Office_Join_Date_Time__c)- In this example, we passed the custom field called “Office_Join_Date_Time__c” to the Salesforce DATETIMEVALUE() formula.
- Now, this formula will convert the text string date and time into Salesforce date and time value.
- Let’s say we have passed the datetime value as “2023-04-03T10:30:00Z”, now the formula will convert it and return it as 2023-04-03T10:30:00Z.
If we want to add a specific number of months to date value, we need to use the ADDMONTHS() function. So, let’s proceed and learn about the ADDMONTHS function in detail.
Salesforce ADDMONTHS() function
ADDMONTHS() formula function in Salesforce is used to add a specific number of months to a given date. This function will return the result in the date value datatype as the new date.
The syntax of the function is as given below:
ADDMONTHS(date, numberofmonthadded)- This function takes two parameters, the first one is the date in “YYYY-MM-DD” format and the second one is the number of months to be added.
- Note: The number of months to be added can be a positive or negative integer value.
Let’s see a simple example, of where we can use the ADDMONTH() function.
Suppose, we calculate on which date of the previous two months the order is delivered. So, for this, we will use the ADDMONTH() function.
Here, is the formula:
ADDMONTHS(TODAY(), -2)- In this example, we will return a date value that is two months before today’s date.
- For this, here we will use the ADDMONTHS function and subtract 2 or we can say add -2 to today’s date value calculated using the TODAY() function.
With this, we have learned all the functions that covered the date value advance formula in detail.
You may also like:
- Salesforce Formula Add Year to Date
- How to add Formula Field in Salesforce Report
- Add Hour to DateTime Field in Salesforce
Conclusion
In conclusion, we have learned what is the advance formula in Salesforce. In addition, we have also learned about the Salesforce advance date formula.
Moreover, we have gone through various Salesforce Date Formula Functions such as ‘Date’, ‘Year’, ‘Day’, ‘Weekday’, ‘Dayofyear’, etc with syntax and examples.
In addition to this, the following are the topics that we have discussed:
- Salesforce Advance Formula Function
- Salesforce Date Formula Function
- Applications of Date Formula Functions in Salesforce
- Common used Date Formula Functions in Salesforce
I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.