# 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 February

Copyright salesforcecom inc All rights reserved Salesforce is a registered trademark of salesforcecom 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 are formatted in the users locale when viewed in reports and record detail pages

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

### 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 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

Finding the Day Month or Year from a Date

### EDITIONS

### Use the functions DAY date MONTH date and YEAR date to return their

### respective numerical values Replace date with a value of type Date eg TODAY

Available in both Salesforce

Classic and Lightning

Experience

### To use these functions with DateTime values first convert them to a date with the DATEVALUE

### function For example DAY DATEVALUE datetime

Available in All Editions

Finding Out if a Year Is a Leap Year

### This formula determines whether or not 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

Finding 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

in which date falls 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 example below illustrates how you can find a dates quarter if Q starts in February instead of January

### CEILING MONTH date

If you want to 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

Finding 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

You can find the current week by determining how many days there have been in the current year and dividing 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 week of January

Finding 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

Finding 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

Displaying 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

Finding and Displaying the Day of the Week From a Date

### To find the day of the week from a Date value use a known Sunday eg January and subtract it from the date eg 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 The formula below finds the result and then returns the text name of that

day

### CASE

### MOD date DATE

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Note that this formula only works for dates after If youre working with older dates use the same process with any Sunday

prior to your earliest date eg

You can also 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

Like the formula for getting 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 etc

Finding 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 youll find the date of the dayofweek The IF statement in this formula handles cases where the

dayofweek is prior to the day of the week of the date value eg 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

Finding 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 slightly if you want to determine a date a certain number of days in the past For example say you want a formula to

return true if some date field is more than days prior to the current date and false otherwise This formula does just that

### TODAY date

Finding the Number of Business Days Between Two Dates

Calculating how many business days passed between two dates is slightly more complex than calculating total elapsed days 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 the date youre examining These values are multiplied by five for a fiveday work week and

then the difference between them is taken to calculate business days

### FLOOR

### FLOOR

### date DATE MIN MOD date DATE

### date DATE MIN MOD date DATE

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

Sample Date Formulas

Adding 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 five days to a date the

formula is date

Adding years to a date is fairly simple but you do need to check that the future date is valid That is adding five years to February a

leap year results in an invalid date The following formula adds numyears to date by checking if the date is February and if

the future date is not in a leap year If these conditions hold true the formula returns March in the future year Otherwise the formula

sets the Date to the same month and day numyears in the future

### IF

### AND

### MONTH date

### DAY date

### NOT

### OR

### MOD YEAR date

### AND

### MOD YEAR date

### MOD YEAR date

### DATE YEAR date numyears

### DATE YEAR date numyears MONTH date DAY date

Adding months to a date is slightly more complicated as months vary in length and the cycle of months restart with each year Therefore

### a valid day in one month January might not be valid in another month February A simple solution is to approximate each

months length as days

date Numbermonths

While this formula is a good estimate it doesnt return an exact date For example if you add two months to April using this method

the formula will return June instead of June Returning an exact date depends on your organizations preference For example

when you add one month to January should it return February the last day of the next month or March days after January

This formula does the following

Returns March if the future month is a February and the day is greater than This portion of the formula performs the same for

both leap and nonleap years

Returns the first day of the next month if the future month is April June September or November and the day is greater than

Otherwise it returns the correct date in the future month

This example formula adds two months to a given date You can modify the conditions on this formula if you prefer different behaviors

for dates at the end of the month

### DATE

### YEAR date FLOOR MONTH date

### MOD MONTH date

### IF DAY date CASE MOD MONTH date

Sample Date Formulas

### IF DAY date CASE MOD MONTH date

### DAY date

If youre using these formulas for expiration dates you might want to subtract a day from the return value to make sure that some action

is completed before the calculated date

Adding 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

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 less business days The tip for getting the day of the week might be useful if you need to adjust this formula

Finding 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

Sample Date Formulas

### 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

### MID TEXT datetime TZoffset

### MID TEXT datetime TZoffset

### IF

### VALUE MID TEXT datetime TZoffset

### AM

### PM

### When working with time in formula fields you need to consider the time difference between your organization and GMT See A Note

About DateTime and Time Zones on page for help understanding the time zone offset used in this formula