Date/Time & Advanced Functions for Formula Fields in Salesforce
Introduction
Hello everyone, Welcome back. We hope you are doing well. In our last blog, we discussed Formula Fields and Formula Operators. We also saw various formula functions allowed to use in Formula Fields. Today we will discuss these formula functions in detail.
Just to make you recall formula functions can only be used in Advanced Formulas and are categorized into five types, i.e.
Date & Time
Text
Math
Logical
Advanced
Now, in this blog, we will discuss the Date & Time and Advanced functions in detail, along with their use cases. So without further ado, let’s start.
To explain the purpose, we have created a different custom object for each category where we will create another formula field with each formula function. For example, we have created a Date and Time object for the Date & Time category with one date type field, “Date Input by User,” to provide its input.
Make it stand out
Date & Time Functions
AddMonths Function
As the name suggests this function adds a number of months to the current date. The syntax of this function is -
So, from the syntax, it's clear that the function accepts two parameters, a date, and a number and it returns a date.
For example, we have created two fields, a formula field named “Date after Adding Months” and a number field “Months to Add” to accept the number of months to be added to the “Date Input by User” field.
The formula of our formula field would look as follows-
ADDMONTHS(Date_Input_by_User__c, Months_to_Add__c)
Now, it's obvious this function would add the number of months specified, but it has some interesting impact on the day and year part of the date. The following screenshots explain the same. Note that the Date is in mm/dd/yyyy format.
Make it stand out
If you will notice the dates in (a), you will notice that though the Date input by the user is different, it is resulting in the same date once the same number of months are added to the date. The reason for this is, if the specified date in the addmonths function is the last day of the month, the resulting date is the last day of the resulting month. Otherwise, the result has the same date component as the specified date.
The same thing can be noticed in (b) as well along with one more point that addmonths function can make changes to the year part of the Date if required for the specified date.
It’s not necessary to specify a field name or positive number for adding months, you can specify a constant and even a negative number in the function. In that case, instead of adding months, it will subtract months from the specified date.
Though you can even specify real numbers like 0.5 in the function it will have no impact on the formula as when calculating the months it will be considered as 0 or in other words, the decimal part would be truncated while calculating the value of the formula field.
Now, this function has various use cases and hence it is the most used function under the Date & Time category. A few of the use cases for this function can be for calculating “End Date”, “Reminder Date”, “Taxation Date” etc. basically at any place where you need to find a date by adding or subtracting the number of months from a specific date.
Date, Day, Month & Year Functions
So, we can clearly add months to a specific date, but if we want to add days or years to a specific date. Well, these functions can play a crucial role in those calculations. So, let's talk about these functions one by one.
Now, the DATE function is used to return a date value from numbers specified in the year, month, and day. The syntax for this function is -
This function returns a blank value on the detail page in case of the returning date due to the specified value is not valid. For example, if we try to get a date as 31st April.
The DAY, MONTH, and YEAR functions are used to fetch these parts from a specified date. That means it's exactly the complementary function of the DATE function. The syntax for these functions is as follows. So, from the syntax, it’s quite clear that we can’t pass a Datetime value to the DAY, YEAR, and MONTH functions.
Now, as we told you earlier we can definitely use a combination of these functions together in case we want to add days or years in a specified date, but we would recommend not to use it for adding days as it might lead you to no value or complex formulas to get correct dates.
If you want to add days, using the ADD(+) operator to a specific date can give you a more accurate result, however, using them for adding years is completely ok. There are some use cases of these functions as well like generating new dates from predefined or calculated values of days, months, and years or any specific business logic. For example, we have created the following formula fields to explain to you the purpose and use of these functions.
“Next month same Day” field
DATE(YEAR(Date_Input_by_User__c ), MONTH(Date_Input_by_User__c) + 1, DAY(Date_Input_by_User__c))
“10 years after on Same Date”
DATE(YEAR(Date_Input_by_User__c ) + 10, MONTH(Date_Input_by_User__c) , DAY(Date_Input_by_User__c) )
“Specific Date Every Year”
DATE(YEAR(TODAY()), MONTH(Date_Input_by_User__c) , DAY(Date_Input_by_User__c) )
“Adding 15 Days”
DATE(YEAR(Date_Input_by_User__c ), MONTH(Date_Input_by_User__c) , DAY(Date_Input_by_User__c) + 15)
OR
Date_Input_by_User__c + 15
Keep note that the MONTHS function returns numbers and not words though we can use this function with CASE (Logical function to return the name of the months). Definitely, we will deal with them later on in a different blog. For now have a look at the following screenshots, where we have also used a Datetime field input by the user and keep a note that for the formula fields we have used numbers (Decimal part 0) as DAY, MONTHS, and YEAR functions always returns in number. The formula fields that we have created are -
Day for Date, Month for Date, and Year for Date
DAY(Date_Input_by_User__c) and MONTH(Date_Input_by_User__c)and YEAR(Date_Input_by_User__c)
Day for Date time, Month for Date time and Year for Datetime
DAY(Date_Time_Input_By_User__c) and MONTH(Date_Input_by_User__c) and YEAR(Date_Time_Input_By_User__c)
Today, Now, TimeNow, DateValue, DateTimeValue & TIMEVALUE Functions
If you have noticed, we have used TODAY function in the “Specific Date Every Year” field, which actually returns the current date according to the user’s timezone of your org, and in our formula field, we have used it with YEAR to return the current year to our formula field. The NOW function and the TIMENOW function are quite similar to the TODAY function, the difference is that the NOW returns the current date and time in the form of Datetime type, whereas the TIMENOW returns the current time only in the form of Time type.
So, to summarize the things TODAY, NOW and TIMENOW functions deal with current date and time, where NOW returns in Datetime, TODAY returns in Date only and TIMENOW returns in Time only. Now, let's see their syntax -
So, with the syntax, it is also clear now that all these three functions do not accept any parameter but just like any function the parenthesis is compulsory with all of them. One more thing you should note is that since all these three functions are dealing with the current date and time, the value of the formula field will change as soon as the record or listview is viewed.
Make sure about the following points, while using these functions -
Do not remove the parentheses and keep the parentheses empty.
Use a date field with a TODAY function and use a date/time field with a NOW function.
You can use addition and subtraction operators with a TODAY function and other date fields to return a number, representing the number of days.
You can use addition and subtraction operators with a NOW function and other date/time fields to return a number, representing the number of days.
You can use addition and subtraction operators with a TODAY function and numbers to return a date.
You can use addition and subtraction operators with a NOW function and numbers to return a date and time.
The displayed value is based on the organization’s Locale settings.
For explaining these functions we have created the following fields, formula used for these fields and their result is also shown with screenshots.
Current Date, Current Date & Time and Current Time
TODAY()
NOW()
TIMENOW()
Age of Record with Date and Age of Record with Datetime
TODAY() - DATEVALUE( CreatedDate )
NOW() - CreatedDate
Ten Days from Now with Date and Ten Days from Now with Datetime
Current_Date__c + 10 and Current_Date_Time__c + 10
Now, let’s talk about DATEVALUE and DATETIMEVALUE functions. Both these functions accept an expression in the form of date/time, text value, merge field, or expression. The DATEVALUE function then converts that expression into date type and the DATETIMEVALUE function converts the expression into datetime type. The TIMEVALUE function converts the expression into time type.
The syntax for these functions are as follows -
Make sure of the following points while working with these functions -
If the field referenced in the function isn't a valid text or date/time field, the formula field displays #ERROR!
When entering a date, surround the date with quotes and use the following format: YYYY-MM-DD, that is, a four-digit year, two-digit month, and two-digit day.
If the expression doesn't match valid date ranges, such as the MM isn't between 01 and 12, the formula field displays #ERROR!
Dates and times are always calculated using the user’s time zone, except in list views, reports, and related lists. These items calculate dates and times using Coordinated Universal Time.
DATETIMEVALUE is always calculated using GMT time zone and can’t be changed.
When entering a specific date, surround the date with quotes and use the following format: YYYY-MM-DD, that is, a four-digit year, two-digit month, and two-digit day.
If the expression doesn't match valid date ranges, such as the MM isn't between 01 and 12, the formula field displays #ERROR!
The displayed value is based on the organization’s Locale settings.
Don’t use TIMEVALUE on a time field. A time field’s value is already in time format.
When entering a specific time, surround the time with quotes and use the following format: HH:MM:SS. MILS.
For explaining these functions we have created the following fields, formula used for these fields and their result is also shown with screenshots.
Date from Expression and Date from Datetime
DATEVALUE(“2022-03-19”) and DATEVALUE(CreatedDate)
Date Time from Expression and Date Time from Datetime
DATETIMEVALUE("2022-02-15 20:30:00") and DATETIMEVALUE(CreatedDate)
Time from Expression and Time from Datetime
TIMEVALUE("20:30:45.125") and TIMEVALUE(CreatedDate)
Hour, Minute, Second & MilliSecond & WeekDay Functions
HOUR, MINUTE, SECOND, and MILLISECOND functions are used to get hour, minute, second, and milliseconds part of a time. The syntax of these functions are as follows -
The WeekDay function returns the day of the week for the given date, using 1 for Sunday, 2 for Monday, through 7 for Saturday and the syntax of this function is as follows -
Definitely, we can use a CASE function with this function as well, if we want to show the name of the weekday.
For explaining these functions we have created the following fields, formula used for these fields and their result is also shown with screenshots.
Hr, Min, Sec, M_Sec and W_Day
HOUR(TIMENOW())
MINUTE(TIMENOW())
SECOND(TIMENOW())
MILLISECOND(TIMENOW())
WEEKDAY(Current_Date__c)
Advanced Function
CurrencyRate Function
It returns the conversion rate to the corporate currency for the given currency ISO code. If the currency is invalid, returns 1.0
The value of this function depends on the conversion rate set in the org.
Please find the following screenshot for the same. The conversion rate is not set for INR and when we used the same for the Indian Con Rate field, we get 1.0
In our next blog, we will discuss text functions. See you soon!👋
Resources
https://help.salesforce.com/s/articleView?id=sf.tips_on_building_formulas.htm&type=5
https://help.salesforce.com/s/articleView?id=sf.customize_functions.htm&type=5
