Date and Time

1. MINUTES

Converts number into minutes value.

MINUTES(parameter)

Replace parameter with the number value to convert it to minutes.

{Opportunity.RFQ_Date__c} + MINUTES(30) will add 30 minutes to RFQ_Date__c value.

If do not convert number to minutes, error will occur.

2. HOURS

Converts number into hours value.

HOURS(parameter)

Replace parameter with the number value to convert it to hours.

{Opportunity.RFQ_Date__c} + HOURS(12) will add 12 hours to RFQ_Date__c value.

If do not convert number to hours, error will occur.

3. DATE

Returns a date value from year, month, and day values you enter.

DATE(year, month, day)

Replace:

year with a four-digit year;

month with a two-digit month;

day with a two-digit day.

DATE(2016, 10, 10) returns October 10, 2016 as a date value.

4. DATETIME

Returns a date value from year, month, day, hours, minutes and seconds values in the local time zone of the current user.

DATETIME(year, month, day, hours, minutes, seconds)

Replace parameters with integer values of year, month, day, hours, minutes and seconds.

DATETIME(2005, 1, 2, 20, 30, 0) returns January 2, 2005, 8:30 PM as a datetime value.

5. DATETIMEGMT

Returns a date value from year, month, day, hours, minutes, seconds in GMT datetime value.

DATETIMEGMT(year, month, day, hours, minutes, seconds)

Replace parameters with integer values of year, month, day, hours, minutes and seconds.

DATETIMEGMT(2005, 1, 2, 20, 30, 0) returns January 2, 2005, 8:30 PM GMT as a datetime value.

6. DATEVALUE

Returns a date value for a date, datetime or text expression in the local time zone of the current user.

DATEVALUE(datetime_or_string)

Replace datetime_or_string with a date, datetime or text value.

DATEVALUE('11/15/2015') returns November 15, 2015 as a date value.

7. DATETIMEVALUE

Returns a datetime value for a date, datetime or text expression in the local time zone of the current user.

DATETIMEVALUE(date_or_string)

Replace date_or_string with a date, datetime or text value.

DATETIMEVALUE('2005-11-15 17:00:00') returns November 15, 2005 5:00 PM as a datetime value.

8. DATETIMEVALUEGMT

Returns a date value for a date, datetime or text expression in the GMT time zone.

DATETIMEVALUEGMT(datetime_or_string)

Replace datetime_or_string with a date/datetime or text value.

DATETIMEVALUEGMT('2015-11-15 17:00:00') returns November 15, 2005 5:00 PM as a datetime value in GMT time zone.

9. DATETIMETOGMT

Converts datetime value from the local time zone of a context user to the Greenwich Mean Time value with datetime type.

DATETIMETOGMT(datetime_object)

Replace datetime_object with a datetime value in context user local time zone.

Let current user local datetime value is 6/9/2015 10:09:14 PST.

DATETIMETOGMT({$System.DateTime}) returns 6/9/2015 17:09:14 GMT as a datetime value.

10. ADDDAYS

Returns a new date or datetime value that adds the specified number of days to the value of this instance.

ADDDAYS(date/datetime, number_of_days, boolean_consider_weekends)

Replace:

date/datetime with the date or datetime you want to add days to;

number_of_days with integer number of days that needs to be added to original value;

boolean_consider_weekends with true if calculation should exclude Saturday and Sunday, or false if it should not.

ADDDAYS(DATE(2019, 02, 12), 2, false) will return 2019-02-14 00:00:00.

11. ADDMONTHS

Adds the given number of months to the date or datetime value.

ADDMONTHS(date/datetime, number_of_months)

Replace:

date/datetime with the date or datetime you want to add months to;

number_of_months with the integer number of months that needs to be added to original value.

ADDMONTHS(DATE(2019, 02, 12), 5) will return 2019-07-12 00:00:00.

12. ADDYEARS

Adds the given number of years to the date or datetime value.

ADDYEARS(date/datetime, number_of_years)

Replace:

date/datetime with the date or datetime you want to add years to;

number_of_years with the integer number of years that needs to be added to original value.

ADDYEARS(DATE(2019, 02, 12), 5) will return 2024-02-12 00:00:00.

13. ADDINTERVAL

Adds time interval to the datetime based on the business hours for the current Salesforce organization.

To access business hours records navigate to Setup > Company Profile > Business Hours.

ADDINTERVAL(datetime, interval_string, business_hours_id_optional)

Replace:

datetime parameter with a datetime value;

interval_sting with time interval in the '1d 1h 1m' format ;

business_hours_id_optional with an ID of a business hours record used in your organization.

Note: a business_hours_id_optional parameter is optional.

If pass an empty string or null, interval will be added using 24-hours day, 7-days week, 365-days year.

Let current system datetime value is Tuesday, 04/01, 8 AM and business hours are set to 8 AM to 11 AM, Monday to Friday.

FORMAT(ADDINTERVAL({$System.DateTime}, '5h 30m', 'id_of_business_hours_record')) will return Wednesday, 04/02, 10:30 AM value.

14. SUBTRACTINTERVAL

Subtracts time interval from the datetime based on the business hours for the current Salesforce organization.

To access business hours records navigate to Setup > Company Profile > Business Hours.

SUBTRACTINTERVAL(datetime, interval_string, business_hours_id_optional)

Replace:

datetime parameter with a datetime value;

interval_sting with time interval in the '1d 1h 1m' format;

business_hours_id_optional with an ID of a business hours record used in your organization.

Note: a business_hours_id_optional parameter is optional.

If pass an empty string or null, interval will be subtracted using 24-hours day, 7-days week, 365-days year.

FORMAT(SUBSTRACTINTERVAL(DATETIME(2019, 03, 12, 11, 0, 0), '3h 30m')) will return 3/12/2019 7:30 AM.

15. NORMALIZEDATE

Moves a date or datetime to the closer working day according to business hours.

To access business hours records navigate to Setup > Company Profile > Business Hours.

NORMALIZEDATE(date/datetime, business_hours_id_optional)

Replace:

date/datetime parameter with value you need to normalize;

business_hours_id_optional with an ID of a business hours record used in your organization.

Note: a business_hours_id_optional parameter is optional.

If pass an empty string or null, default business hours setting for the organization will be used to calculate date or datetime.

Let current system datetime value is Saturday, 03/16, 2 PM and business hours are set to 8 AM to 11 AM, Monday to Friday.

NORMALIZEDATE({$System.DateTime}, id_of_business_hours_record) will return a datetime value for Monday, 3/18 8:00 AM.

16. STARTOFDAY

Moves a date/datetime to the start of working day according to business hours.

To access business hours records navigate to Setup > Company Profile > Business Hours.

STARTOFDAY(date/datetime, business_hours_id_optional)

Replace:

date/datetime parameter with value you need to calculate;

business_hours_id_optional with an id of a business hours record used in your organization.

Note: a business_hours_id_optional parameter is optional.

If pass an empty string or null, default business hours setting for the organization will be used to calculate date or datetime.

Let current system datetime value is Wednesday, 03/13, 2 PM and business hours are set to 8 AM to 11 AM, Monday to Friday.

STARTOFDAY({$System.DateTime}, id_of_business_hours_record) will return datetime value for Wednesday, 3/13 8:00 AM.

17. ENDOFDAY

Moves a date/datetime to the end of working day according to business hours.

To access business hours records navigate to Setup > Company Profile > Business Hours.

ENDOFDAY(date/datetime, business_hours_id_optional)

Replace:

date/datetime parameter with value you need to calculate;

business_hours_id_optional with an id of a business hours record used in your organization.

Note: a business_hours_id_optional parameter is optional. If pass an empty string or null, default business hours setting for the organization will be used to calculate date or datetime.

Let current system datetime value is Wednesday, 03/13, 2 PM and business hours are set to 8 AM to 11 AM, Monday to Friday.

ENDOFDAY({$System.DateTime}, id_of_business_hours_record) will return datetime value for Wednesday, 3/13, 11AM.

18. FORMAT

Returns a value containing a date expression formatted according to given parameter.

FORMAT(date/datetime, format_string_optional)

Replace:

date/datetime with the original date or datetime value;

optional parameter format_string_optional with the valid format expression.

Check supported formats here: http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html).

FORMAT(DATE(2019, 02, 12)) will return 2/12/2019.

FORMAT(DATETIME(2019, 02, 12, 11, 23, 0)) will return 2/12/2019 11:23 AM

FORMAT(DATETIME(2019, 02, 12, 11, 23, 0), 'h:mm a') will return 11:23 AM.

19. FORMATGMT

Returns a value containing an expression formatted according to given parameter in GMT time value.

FORMATGMT(date/datetime, format_string_optional)

Replace:

date/datetime with the original date or datetime value;

optional parameter format_string_optional with the valid format expression.

Check supported formats here: http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html).

Let organization time zone is Eastern Standard Time.

FORMATGMT(DATETIME(2019, 03, 12, 11, 23, 0)) will return 3/12/2019 3:23 PM.

20. STARTOFWEEK

Returns date or datetime representing the first day of week for the date passed to the function in the local time zone of the current user.

STARTOFWEEK(date/dateTime)

Replace date/dateTime parameter with the date or datetime value which start date of the week is to be determined.

STARTOFWEEK({Opportunity.createdDate}) will return start of the week date for the value of createdDate in GMT timezone.

Let current system datetime value is Wednesday, 03/13, 2 PM.

STARTOFWEEK({$System.DateTime}) will return datetime value for Monday, 3/11, 2 PM.

21. DAYSBETWEEN

Returns number of days between two date or datetime values.

DAYSBETWEEN(date/datetime, date/datetime, ignoreWeekends)

Replace:

first two parameters with the two date/datetime values;

boolean ignoreWeekends with true if calculation should exclude Saturdays and Sundays, or false if it should not.

Let createdDate equals 2001-07-04 and closedDate equals 2001-07-10

DAYSBETWEEN({Opportunity.createdDate}, {Opportunity.closedDate}, false) will return 5.

22. DATEDIFF

Returns number of minutes, hours and days between date/datetime values.

To access business hours records navigate to Setup > Company Profile > Business Hours.

DATEDIFF(date/datetime, date/datetime, business_hours_id_optional, result_type_optional, hours_in_day_optional)

Replace:

first two parameters with two dates;

business_hours_id_optional optional parameter with an ID of a business hours record used in your organization;

result_type_optional optional parameter with one of the following values: 'Duration', 'Hours', 'Days' (this parameter is case-insensitive);

Duration format looks like this: 1d 7h 45m.

hours_in_day_optional is an optional parameter that can be used when user passes 'Duration' or 'Days' as a result_type_optional - it defines number of hours in a day for function calculation.

Notes:

A business_hours_id_optional parameter is optional. If omit this parameter or pass null, function will calculate result using 24-hours day, 7-days week, 365-days year.
Function with 'Hours' result type will return full number of hours rounded down. This can be useful to avoid lates in calculated schedules, etc.
If hours_in_day_optional is used, business_hours_id_optional should be set to null (optional parameters can be omitted only if they are last, otherwise they should be set to null).

Let createdDate = 2001-07-04 and closedDate = 2001-07-05

DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}) returns 1440 (minutes).

Let createdDate = 2020-06-29 and closedDate = 2020-07-01T03:30:05Z, and business hours are set as 8 hours per day from Monday to Friday.

DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}, business_hours_id) returns 960 (minutes).

Let createdDate = 2020-06-29 and closedDate = 2020-07-01T03:30:05Z.

DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}, null, DURATION) will return 44h 30m.

Let createdDate = 2020-06-29 and closedDate = 2020-07-01T03:30:05Z.

DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}, null, Hours) will return 44 (integer number of hours).

Let createdDate = 2001-07-04 and closedDate = 2001-07-05.

DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}, false, 'Duration', 8) returns 3d.

23. YEAR

Returns the year component of a date or datetime value.

YEAR(parameter)

Replace parameter with the date/datetime value.

Let RFQ_Date__c = 2020-07-01T03:30:05Z.

YEAR({Opportunity.RFQ_Date__c}) returns 2020 (integer representing year component of RFQ_Date__c field value).

24. MONTH

Returns the month component of a date or datetime value.

MONTH(parameter)

Replace parameter with the date/datetime value.

Let RFQ_Date__c = 2020-07-01T03:30:05Z.

MONTH({Opportunity.RFQ_Date__c}) returns 7 (integer representing month component of RFQ_Date__c field value).

Last updated 2 years ago