Formula Date Time Tipsheet
DOWNLOAD
First things first !
To download this implementation guide, click the download button below.
If you need more information about the implementation guide, you can read the Table of Contents below.
Using Date and DateTime in
Formulas
Salesforce Spring
salesforcedocs
Last updated November
Copyright Salesforce Inc All rights reserved Salesforce is a registered trademark of Salesforce Inc as are other
names and marks Other marks appearing herein may be trademarks of their respective owners
CONTENTS
Using Date DateTime and Time Values in Formulas
Sample Date Formulas
USING DATE DATETIME AND TIME VALUES IN FORMULAS
Date formulas are useful for managing payment deadlines contract ages or any other features of
your organization that are time or date dependent
EDITIONS
Two data types are used for working with dates Date and DateTime One data type Time is
independent of the date for tracking time such as business hours Most values that are used when
working with dates are of the Date data type which store the year month and day Some fields
such as CreatedDate are DateTime fields meaning they not only store a date value but also a time
value stored in GMT but displayed in the users time zone Date DateTime and Time fields are
formatted in the users locale when viewed in reports and record detail pages A Time values
precision is in milliseconds A DateTime values precision is in seconds
Available in both Salesforce
Classic and Lightning
Experience
Available in All Editions
You can use operations like addition and subtraction on Date DateTime and TIme values to calculate a future date or elapsed time
between two dates or times If you subtract one date from another for example the resulting value will be the difference between the
two initial values in days Number data type The same operation between two DateTime values returns a decimal value indicating
the difference in number of days hours and minutes The same operation between two Time values returns millisecond
For example if the difference between two DateTime values is that means the two values are separated by five days hours
of a day and minutes of a day You can also add numeric values to Dates and DateTimes For example the operation TODAY
returns three days after todays date For more information and examples of working with dates see the list of Sample Date Formulas
Throughout the examples the variables date and datetime are used in place of actual Date and DateTime fields or values
Keep in mind that complex date functions tend to compile to a larger size than text or number formula functions so you might run into
issues with formula compile size See Tips for Reducing Formula Size for help with this problem
TODAY NOW and TIMENOW
The TODAY function returns the current day month and year as a Date data type This function is useful for formulas where you are
concerned with how many days have passed since a previous date the date of a certain number of days in the future or if you just want
to display the current date
The NOW function returns the DateTime value of the current moment Its useful when you are concerned with specific times of day
as well as the date
The TIMENOW function returns a value in GMT representing the current time without the date Use this function instead of the
NOW function if you want the current hour minute seconds or milliseconds This value is useful for tracking time like work shifts or
elapsed time
For details on how to convert between Date values and DateTime values see Converting Between DateTime and Date on page
The DATE Function
The DATE function returns a Date value given a year month and day Numerical YMD values and the YEAR MONTH and
DAY functions are valid parameters for DATE For example DATE returns June Similarly DATE
YEAR TODAY MONTH TODAY returns the Date value of the first day three months from today in the
current year assuming the date is valid for example the month falls between and
Using Date DateTime and Time Values in Formulas
If the inputted YMD values result in an invalid date the DATE function returns an error so error checking is an important part of
working with Date values You can read about methods for handling invalid dates in Sample Date Formulas
Converting Between DateTime and Date
Date and DateTime arent interchangeable data types so when you want to perform operations between Date and DateTime values
you need to convert the values so they are both the same type Some functions such as YEAR MONTH and DAY also only
work on Date values so DateTime values must be converted first
Use the DATEVALUE datetime function to return the Date value of a DateTime For example to get the year from a
DateTime use YEAR DATEVALUE datetime
You can convert a Date value to a DateTime using the DATETIMEVALUE date function The time will be set to am in
Greenwich Mean Time GMT and then converted to the time zone of the user viewing the record when its displayed For a user located
in San Francisco DATETIMEVALUE TODAY returns pm on the previous day during Daylight Saving Time rather than
am of the current day See A Note About DateTime and Time Zones on page for more information
Converting Between DateTime and Time
The TIMEVALUE function returns a Time data type value in HHMMSSMS hoursminutessecondsmilliseconds format using a
hour clock Numerical HMSMS values and the HOUR MINUTE SECONDS and MILLISECONDS functions are
valid parameters for TIMEVALUE
Use the TIMEVALUEvalue function to return the Time value of a DateTime type text merge field or expression For example
extract the time from a ClosedDate DateTime value with TIMEVALUEClosedDate
Converting Between Date and Text
If you want to include a date as part of a string wrap the Date value in the TEXT function to convert it to text For example if you
want to return todays date as text use
Todays date is TEXT TODAY
This returns the date in the format YYYYMMDD rather than in the localedependent format You can change the format by extracting
the day month and year from the date first and then recombining them in the format you want For example
Todays date is TEXT MONTH date TEXT DAY date TEXT YEAR
date
You can also convert text to a Date so you can use the string value with your other Date fields and formulas Youll want your text to be
formatted as YYYYMMDD Use this formula to return the Date value
DATEVALUE YYYYMMDD
Converting Between DateTime and Text
You can include DateTime values in a string using the TEXT function but you need to be careful of time zones For example
consider this formula
The current date and time is TEXT NOW
Using Date DateTime and Time Values in Formulas
In this formula NOW is offset to GMT Normally NOW would be converted to the users time zone when viewed but because its
been converted to text the conversion wont happen So if you execute this formula on August st at PM in San Francisco time
GMT the result is The current date and time is Z
When you convert a DateTime to text a Z is included at the end to indicate GMT TEXT datetime returns Z if the field is
blank So if the DateTime value youre working with might be blank check for this before converting to text
IF
ISBLANK datetime
TEXT datetime
To convert a string to a DateTime value use DATETIMEVALUE passing in a string in the format YYYYMMDD HHMMSS This
method returns the DateTime value in GMT
Converting Between Time and Text
If you want to include time as part of a string wrap the Time value in the TEXT function to convert it to text For example if you
want to return the current time as text use
The time is TEXT TIMENOW
This function returns the time in the format HHMMSSMS
You can also convert text to a Time data type so you can use the string value with your other Time fields and formulas Format your text
as HHMMSSMS on a hour clock Use the TIMEVALUE function
TIMEVALUE
A Note About DateTime and Time Zones
Date and DateTime values are stored in GMT When a record is saved field values are adjusted from the users time zone to GMT and
then adjusted back to the viewers time zone when displayed in record detail pages and reports With Date conversions this doesnt
pose a problem since converting a DateTime to a Date results in the same Date value
When working with DateTime fields and values however the conversion is always done in GMT not the users time zone Subtracting
a standard DateTime field from another isnt a problem because both fields are in the same time zone When one of the values in the
calculation is a conversion from a Text or Date value to a DateTime value however the results are different
Lets say a San Francisco user enters a value of AM on August in a custom DateTime field called DateTimec This
value is stored as Z because the time difference in Pacific Daylight Time is GMT At pm PDT on August
st the user views the record and the following formula is run
DateTimec NOW
In the calculation NOW is Z and then subtracted from Z to return the expected result of
hours
Suppose that instead of NOW the formula converts the string to a DateTime value
DateTimec DATETIMEVALUE
In this case DATETIMEVALUE is Z and returns a result of or
hours
Using Date DateTime and Time Values in Formulas
Theres no way to determine a users time zone in a formula If all of your users are in the same time zone you can adjust the time zone
difference by adding or subtracting the time difference between the users time zone and GMT to your converted values However since
time zones can be affected by Daylight Saving Time and the start and end dates for DST are different each year this is difficult to manage
in a formula We recommend using Apex for transactions that require converting between DateTime values and Text or Date values
SAMPLE DATE FORMULAS
Use the sample formulas in this topic to manipulate and perform calculations with date and time
EDITIONS
Find the Day Month or Year from a Date
Available in both Salesforce
Classic and Lightning
Experience
Use the functions DAY date MONTH date and YEAR date to return their
numerical values Replace date with a value of type Date for example TODAY
Available in All Editions
To use these functions with DateTime values first convert them to a date with the DATEVALUE
function For example DAY DATEVALUE datetime
Find Out If a Year Is a Leap Year
This formula determines whether a year is a leap year A year is only a leap year if its divisible by or if its divisible by four but not by
OR
MOD YEAR date
AND
MOD YEAR date
MOD YEAR date
Find Which Quarter a Date Is In
For standard quarters you can determine which quarter a date falls in using this formula This formula returns the number of the quarter
that date falls in by dividing the current month by three the number of months in each quarter and taking the ceiling
CEILING MONTH date
The formula for shifted quarters is similar but shifts the month of the date by the number of months between January and the first
quarter of the fiscal year The following example shows how to find a dates quarter if Q starts in February instead of January
CEILING MONTH date
ITo check whether a date is in the current quarter add a check to compare the dates year and quarter with TODAYs year and quarter
AND
CEILING MONTH date CEILING MONTH TODAY
YEAR date YEAR TODAY
Sample Date Formulas
Find the Week of the Year a Date Is In
To find the number of a dates week of the year use this formula
IF
CEILING date DATE YEAR date
CEILING date DATE YEAR date
To find the current week number determine the days to date in the current year and divide that value by The IF statement ensures
that the week number the formula returns doesnt exceed So if the given date is December of the given year the formula returns
even though its more than weeks after the first week of January
Find Whether Two Dates Are in the Same Month
To determine whether two Dates fall in the same month say for a validation rule to determine whether an opportunity Close Date is in
the current month use this formula
AND
MONTH date MONTH date
YEAR date YEAR date
Find the Last Day of the Month
The easiest way to find the last day of a month is to find the first day of the next month and subtract a day
IF
MONTH date
DATE YEAR date
DATE YEAR date MONTH date
Display the Month as a String instead of a Number
To return the month as a text string instead of a number use
CASE
MONTH date
January
February
March
April
May
June
July
August
September
Sample Date Formulas
October
November
December
If your organization uses multiple languages you can replace the names of the month with a custom label
CASE
MONTH date
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
LabelMonthofYear
Find and Display the Day of the Week from a Date
To find the day of the week from a Date value use a known Sunday for example January and subtract it from the date for
example TODAY to get the difference in days The MOD function finds the remainder of this result when divided by to give
the numerical value of the day of the week between Sunday and Saturday This formula finds the result and then returns the text
name of that day
CASE
MOD date DATE
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
This formula only works for dates after If you work with older dates use the same process with any Sunday before to your
earliest date for example
You can adjust this formula if your week starts on a different day For example if your week starts on Monday you can use January
in your condition The new formula looks like this
CASE
MOD date DATE
Monday
Tuesday
Wednesday
Thursday
Friday
Sample Date Formulas
Saturday
Sunday
To get the formula for the name of the month if your organization uses multiple languages you can replace the names of the day of
the week with a variable like LabelDayofWeek and so on
Find the Next Day of the Week After a Date
To find the date of the next occurrence of a particular day of the week following a given Date get the difference in the number of days
of the week between a date and a dayofweek a number where Sunday and Saturday By adding this difference to
the current date you can find the date of the dayofweek The IF statement in this formula handles cases where the
dayofweek is before the day of the week of the date value for example date is a Thursday and dayofweek is a Monday
by adding to the difference
date dayofweek MOD date DATE
IF
MOD date DATE dayofweek
You can substitute either a constant or another field in for the dayofweek value based on your needs
Find the Number of Days Between Two Dates
To find the number of days between two dates date and date subtract the earlier date from the later date date
date
You can alter this formula slightly if you want to determine a date thats a certain number of days in the past For example to create a
formula to return true if some date field is more than days before the current date and false otherwise use a formula such as the
following
TODAY date
Find the Number of Weekdays Between Two Dates
Calculating how many weekdays passed between two dates is slightly more complex than calculating total elapsed days In this example
weekdays are Monday through Friday The basic strategy is to choose a reference Monday from the past and find out how many full
weeks and any additional portion of a week have passed between the reference date and your date These values are multiplied by five
for a fiveday work week and then the difference between them is taken to calculate weekdays
FLOOR
FLOOR
date DATE MIN MOD date DATE
date DATE MIN MOD date DATE
Sample Date Formulas
In this formula date is the more recent date and date is the earlier date If your work week runs shorter or longer than five
days replace all fives in the formula with the length of your week
Find the Number of Months Between Two Dates
To find the number of months between two dates subtract the year of the earlier date from the year of the later date and multiply the
difference by Next subtract the month of the earlier date from the month of the later date and add that difference to the value of
the first set of operations
YEARdate YEARdate MONTHdate MONTHdate
Add Days Months and Years to a Date
If you want to add a certain number of days to a date add that number to the date directly For example to add days to a date the
formula is date
If you want to add a certain number of months to a date use this function
ADDMONTHS
For example if you want to add months to a date use this formula
ADDMONTHSdate
If the date that you provide is the last of any month this formula returns the last day of the resulting month
To add a certain number of years to a date use this formula
ADDMONTHSdate numyears
If the date that you provide is February and the resulting year isnt a leap year the formula returns the date as February In this
scenario if you want the resulting date as March use this formula
IF MODYear ADDMONTHSdate numyears ADDMONTHSdate
numyearsADDMONTHSdate numyears
Add Business Days to a Date
This formula finds three business days from a given date
CASE
MOD date
date
date
date
date
date
DATE
This formula finds the day of the week of the date field value If the date is a Wednesday Thursday or Friday the formula adds five
calendar days two weekend days three weekdays to the date to account for the weekend If date is a Saturday you need four
Sample Date Formulas
additional calendar days For any other day of the week Sunday Tuesday simply add three days You can easily modify this formula to
add more or fewer business days The tip for getting the day of the week is useful to use to adjust this formula
Find the Hour Minute or Second from a DateTime
To get the hour minute and second from a DateTime field as a numerical value use the following formulas where TZoffset is the
difference between the users time zone and GMT For hour in hour format
VALUE MID TEXT datetime TZoffset
For hour in hour format
IF
OR
VALUE MID TEXT datetime TZoffset
VALUE MID TEXT datetime TZoffset
VALUE MID TEXT datetime TZoffset
IF
VALUE MID TEXT datetime TZoffset
For minutes
VALUE MID TEXT datetime TZoffset
For seconds
VALUE MID TEXT datetime TZoffset
And to get AM or PM as a string use
IF
VALUE MID TEXT datetime TZoffset
AM
PM
To return the time as a string in HHMMSS APM format use the following formula
IF
OR
VALUE MID TEXT datetime TZoffset
VALUE MID TEXT datetime TZoffset
TEXT VALUE MID TEXT datetime TZoffset
IF
VALUE MID TEXT datetime TZoffset
Sample Date Formulas
MID TEXT datetime TZoffset
MID TEXT datetime TZoffset
IF
VALUE MID TEXT datetime TZoffset
AM
PM
When working with time in formula fields always consider the time difference between your organization and GMT See A Note About
DateTime and Time Zones on page for more information about the time zone offset used in this formula
Find the Elapsed Time Between DateTimes
To find the difference between two Date values as a number subtract one from the other like so date date to return the
difference in days
Finding the elapsed time between two DateTime values is slightly more complex This formula converts the difference between two
DateTime values datetime and datetime to days hours and minutes
IF
datetime datetime
TEXT FLOOR datetime datetime days
TEXT FLOOR MOD datetime datetime hours
TEXT ROUND MOD datetime datetime minutes
Find the Number of Business Hours Between Two DateTimes
The formula to find business hours between two DateTime values expands on the formula to find elapsed business days It works on
