Date Formula Functions in Salesforce : Bijay Kumar
by: Bijay Kumar
blow post content copied from SalesForce FAQs
click here to view original post
**Summary of Salesforce Date Formula Functions** As a Salesforce Administrator, you often help the sales team track inactive opportunities. If an opportunity has had no activity for over 30 days, you can use a formula to flag it for follow-up by comparing its Last Activity Date with the current date using the TODAY() function. This guide discusses various date formula functions available in Salesforce, especially within Salesforce Lightning, and how these functions can be utilized in different contexts like workflow rules, formula fields, and validation rules. ### Key Details: 1. **Advanced Formula Functions**: These are pre-built functions used for calculations involving dates, times, and data conversions. 2. **Date Formula Functions**: These include functions to format, extract, and calculate with dates. Common date functions include: - **TODAY()**: Returns the current date. - **NOW()**: Returns the current date and time. - **DATE(year, month, day)**: Constructs a date from specified year, month, and day. - **YEAR(date)**: Extracts the year from a date. - **MONTH(date)**: Extracts the month from a date. - **DAY(date)**: Extracts the day from a date. - **WEEKDAY(date)**: Returns the day of the week. - **ISOYEAR(date)**: Returns the ISO year. - **ISOWEEK(date)**: Returns the ISO week number. - **DAYOFYEAR(date)**: Returns the day of the year. - **DATEVALUE(datestring)**: Converts a date in string format to a date value. - **DATETIMEVALUE(datetimestring)**: Converts a date and time in string format to a date and time value. - **ADDMONTHS(date, number)**: Adds a specified number of months to a date. ### Applications: - Calculate account age. - Schedule follow-ups or tasks based on specific dates. - Determine expiration dates for contracts or licenses. ### Conclusion: Understanding these date functions enhances your capability to manage and analyze data effectively within Salesforce, supporting the sales team’s efforts. ### Relevant Context: Using these functions effectively can streamline processes and improve productivity by ensuring that important dates and deadlines are managed accurately. ### Hashtags for SEO: #Salesforce #SalesforceAdministrator #DateFunctions #SalesforceTutorial #SalesforceLightning #CRM #DataManagement #SalesforceFormulas #BusinessAutomation #TechTips
While working as a Salesforce Administrator, you may need to assist the sales team in tracking inactive opportunities. For example, if an opportunity has no activity for more than 30 days, you can create a formula that compares the Last Activity Date with the TODAY() function. This will flag opportunities that need follow-up.
In this Salesforce tutorial, we will explore the various date formula functions in Salesforce. Moreover, we will review some of the common date function formulas in Salesforce Lightning.
Salesforce Advance Formula Function
The Advanced Formula function is a pre-built function in Salesforce that we will use to perform calculations, work with dates, times, and data, as well as to convert text.
We can utilize these formula functions in Salesforce workflow rules, formula fields, process builders, validation rules, and other similar contexts. The Salesforce advanced formula functions include logical, mathematical, text, date, and time functions.
Date Formula Functions in Salesforce
As we mentioned above, Salesforce Advanced Formula Functions include Date and Time Functions. We will now discuss the Salesforce Date Formula Function in detail.
The Date formula function is advanced 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.
Applications of Date Formula Functions in Salesforce
Here are some applications of Salesforce Date Formula Functions:
- We can use the DATE formula function to calculate how many years your account has been open.
- The date functions can be used to arrange follow-up actions for a specific day, such as scheduling a phone call, campaign, task, or lead check.
- We can use the date formula function to calculate the number of days between specific date range records.
- To calculate the expiration dates of contracts, licenses, certifications, and products, we utilize the Salesforce date functions.
Commonly Used Date Formula Functions in Salesforce
There are several 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 current time and date.
- 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.
1. Salesforce TODAY() function
The TODAY() formula function in Salesforce is used to return the current date, excluding 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 require any parameters; we simply need to enter 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 employee’s birth date. Therefore, we will use the TODAY() function in the formula field for this purpose.
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 advanced formula function returns a 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.
2. Salesforce NOW() function
The 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 that it returns the date value with time information also.
The syntax of the function is as given below:
NOW()- This function does not require any parameters; it is a simple function that only requires the word ‘NOW’ to be entered 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 the number of days left until the subscription expires. Therefore, we will use the NOW() function in the formula field for this purpose.
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.
3. Salesforce DATE() function
The 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 means 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, passing the year as 2023, the month as 04, and the day as 04, which returns the date as April 4, 2023.
To retrieve the year from a date value, we must use the YEAR() function. So, let’s proceed and learn about the YEAR function in detail.
4. Salesforce YEAR() function
The 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. Therefore, we will use the YEAR() function in the formula field for this purpose.
Here is the formula:
YEAR(Date_Of_Joining__c)- In this example, we will retrieve the year value from the “Date_Of_Joining__c” field, which represents the date of joining.
To retrieve the month from a date value, we need to use the MONTH() function. So, let’s proceed and learn about the MONTH function in detail.
5. Salesforce MONTH() function
The MONTH() formula function in Salesforce is used to return the month of a given date value. It returns a value between 1 and 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. Therefore, we will use the MONTH() function in the formula field for this purpose.
Here is the formula:
MONTH(Invoice_Date__c)- In this example, we will retrieve the month value from the Date of the Invoice, which is stored in the “Invoice_Date__c” field.
- The function returns an integer value between 1 and 12 that represents the month of the date.
To retrieve the day from a date value, we need to use the DAY() function. So, let’s proceed and learn about the DAY function in detail.
6. Salesforce DAY() function
The 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 examine a simple example of how we can utilize the DAY() function.
Suppose we want to extract the day from the date the lead was created. Therefore, we will use the DAY() function in the formula field for this purpose.
Here is the formula:
DAY(Lead_Start_Date__c)- In this example, we will use the DAY() function to retrieve the day value from the “Lead_Start_Date__c” date field. 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.
To retrieve the weekday from a date value, we must use the WEEKDAY() function. So, let’s proceed and learn about the WEEKDAY function in detail.
7. Salesforce WEEKDAY() function
The WEEKDAY() formula function in Salesforce is utilized to get the day of the week for a specific date. It returns the numbers between 1 and 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. Therefore, we will use the WEEKDAY() function in the formula field for this purpose.
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 that it will display the result as 4. ( Thursday is the fourth day of the week ).
To retrieve the ISO year from a date value, we must use the ISOYEAR() function. So, let’s proceed and learn about the ISOYEAR function in detail.
8. Salesforce ISOYEAR() function
The 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 determine the ISO year for a given date. Therefore, we will use the ISOYEAR() function in the formula field for this purpose.
Here is the formula:
ISOYEAR(DATE(2023,04,02))- In this example, we aim to determine the ISO year for the given date “2023-04-02” using the ISOYEAR function.
- This function will return the result as a number, 2023.
To find the ISO week, we can use the ISOWEEK function. So, let’s learn it in detail.
9. Salesforce ISOWEEK() function
The 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 determine the ISO week number for a given date. Therefore, we will use the ISOWEEK() function in the formula field for this purpose.
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 14.
To retrieve the day of the year from a date value, we must use the DAYOFYEAR() function. So, let’s proceed and learn about the DAYOFYEAR function in detail.
10. 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, ranging from 1 to 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 determine on which day of the year the client’s account is created. Therefore, we will use the DAYOFYEAR() function in the formula field for this purpose.
Here is the formula:
DAYOFYEAR(Account_Create_Date__c)- Let’s say the Account is created on April 5, 2023. This formula will then return a day-of-year value of 95. (April 5th is the 95th day of the year 2023).
To convert a date from a string to a date data type, we need to use the DATEVALUE() function. So, let’s proceed and learn about the DATEVALUE function in detail.
11. Salesforce DATEVALUE() function
The DATEVALUE() formula function in Salesforce is used to convert a date stored in a field with a text or string data type to a date data type. 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. Therefore, we will use the DATEVALUE() function for this purpose.
Here is the formula:
DATEVALUE("05/04/2023")- This formula will return a date value of 2023-04-05.
If we want to convert the datetime value from a string to a date datatype, we need to use the DATETIMEVALUE() function. So, let’s proceed and learn about the DATETIMEVALUE function in detail.
12. Salesforce DATETIMEVALUE() function
The 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, and 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’s arrival at the office in a string format, and now we want to convert it into a date and time value. Therefore, we will use the DATETIMEVALUE() function for this purpose.
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 a 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 the same value, 2023-04-03T10:30:00Z.
To add a specific number of months to a date value, use the ADDMONTHS() function. So, let’s proceed and learn about the ADDMONTHS function in detail.
13. 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 is the date in “YYYY-MM-DD” format, and the second 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 examine a simple example of how we can utilize the ADDMONTH() function.
Suppose we calculate the date on which the order is delivered within the previous two months. Therefore, we will use the ADDMONTH() function for this purpose.
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 cover the date value advanced formula in detail.
Conclusion
In conclusion, we have learned what the advanced formula is in Salesforce. Additionally, we have learned about the Salesforce advanced date formula.
Moreover, we have gone through various Salesforce Date Formula Functions such as ‘Date’, ‘Year’, ‘Day’, ‘Weekday’, ‘Dayofyear’, etc, with syntax and examples.
You may also like:
- Salesforce Formula to Calculate Days Between Dates
- Salesforce Formula Field to Calculate Age
- Salesforce Due Date Formula
- Salesforce Last Activity Date Formula
- Salesforce Date Range Formula
The post Date Formula Functions in Salesforce appeared first on SalesForce FAQs.
September 11, 2025 at 06:08PM
Click here for more details...
=============================
The original post is available in SalesForce FAQs by Bijay Kumar
this post has been published as it is through automation. Automation script brings all the top bloggers post under a single umbrella.
The purpose of this blog, Follow the top Salesforce bloggers and collect all blogs in a single place through automation.
============================
Post a Comment