Newer Version Available

This content describes an older version of this product. View Latest

Sample Date Formulas

Use the sample formulas in this topic to manipulate and perform calculations with date and time.

Available in: both Salesforce Classic and Lightning Experience

Available in: All Editions

Find the Day, Month, or Year from a Date

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()).

To use these functions with Date/Time values, first convert them to a date with the DATEVALUE() function. For example, DAY( DATEVALUE( date/time )).

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 it’s divisible by 400, or if it’s divisible by four but not by 100.

1OR( 
2  MOD( YEAR( date ), 400 ) = 0, 
3  AND( 
4   MOD( YEAR( date ), 4 ) = 0,
5    MOD( YEAR( date ), 100 ) != 0
6  )
7)

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 (1–4) by dividing the current month by three (the number of months in each quarter) and taking the ceiling.

1CEILING( MONTH ( date ) / 3 )

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 date’s quarter if Q1 starts in February instead of January.

1CEILING( ( MONTH ( date ) - 1 ) / 3)

ITo check whether a date is in the current quarter, add a check to compare the date’s year and quarter with TODAY()’s year and quarter.

1AND(
2   CEILING( MONTH( date ) / 3 ) = CEILING( MONTH( TODAY() ) / 3 ),
3   YEAR( date ) = YEAR( TODAY() )
4)

Find the Week of the Year a Date Is In

To find the number of a date’s week of the year, use this formula:

1IF( 
2  CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7) > 52,
3  52,
4  CEILING( ( date - DATE( YEAR( date ), 1, 1) + 1) / 7)
5)

To find the current week number, determine the days to date in the current year and divide that value by 7. The IF() statement ensures that the week number the formula returns doesn’t exceed 52. So if the given date is December 31 of the given year, the formula returns 52, even though it’s more than 52 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:

1AND(
2  MONTH( date_1 ) == MONTH( date_2 ),
3  YEAR( date_1 ) == YEAR( date_2 )
4)

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.

1IF(
2  MONTH( date ) = 12,
3  DATE( YEAR( date ), 12, 31 ),
4  DATE( YEAR( date ), MONTH ( date ) + 1, 1 ) - 1 
5)

Display the Month as a String instead of a Number

To return the month as a text string instead of a number, use:

1CASE(
2  MONTH( date ),
3  1, "January",
4  2, "February",
5  3, "March",
6  4, "April",
7  5, "May",
8  6, "June",
9  7, "July",
10  8, "August",
11  9, "September",
12  10, "October",
13  11, "November",
14  "December"
15)

If your organization uses multiple languages, you can replace the names of the month with a custom label:

1CASE(
2  MONTH( date ),
3  1, $Label.Month_of_Year_1,
4  2, $Label.Month_of_Year_2,
5  3, $Label.Month_of_Year_3,
6  4, $Label.Month_of_Year_4,
7  5, $Label.Month_of_Year_5,
8  6, $Label.Month_of_Year_6,
9  7, $Label.Month_of_Year_7,
10  8, $Label.Month_of_Year_8,
11  9, $Label.Month_of_Year_9,
12  10, $Label.Month_of_Year_10,
13  11, $Label.Month_of_Year_11,
14  $Label.Month_of_Year_12
15)

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 7, 1900, 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 7 to give the numerical value of the day of the week between 0 (Sunday) and 6 (Saturday). This formula finds the result and then returns the text name of that day.

1CASE(
2  MOD( date - DATE( 1900, 1, 7 ), 7 ),
3  0, "Sunday",
4  1, "Monday",
5  2, "Tuesday",
6  3, "Wednesday",
7  4, "Thursday",
8  5, "Friday", 
9  "Saturday"
10)

This formula only works for dates after 01/07/1900. If you work with older dates, use the same process with any Sunday before to your earliest date, for example, 01/05/1800.

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 8, 1900 in your condition. The new formula looks like this:

1CASE(
2  MOD( date - DATE( 1900, 1, 8 ), 7 ),
3  0, "Monday",
4  1, "Tuesday",
5  2, "Wednesday",
6  3, "Thursday",
7  4, "Friday",
8  5, "Saturday", 
9  "Sunday"
10)

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 $Label.Day_of_Week_1, 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 day_of_week, a number 0–6 where 0 = Sunday and 6 = Saturday. By adding this difference to the current date, you can find the date of the day_of_week. The IF() statement in this formula handles cases where the day_of_week is before the day of the week of the date value (for example date is a Thursday and day_of_week is a Monday) by adding 7 to the difference.

1date + ( day_of_week - MOD( date - DATE( 1900, 1, 7 ), 7 ) )
2+
3IF( 
4  MOD( date - DATE( 1900, 1, 7 ), 7 ) >= day_of_week,
5  7,
6  0
7)

You can substitute either a constant or another field in for the day_of_week value based on your needs.

Find the Number of Days Between Two Dates

To find the number of days between two dates, date_1, and date_2, subtract the earlier date from the later date: date_1 — date_2

You can alter this formula slightly if you want to determine a date that’s a certain number of days in the past. For example, to create a formula to return true if some date field is more than 30 days before the current date and false otherwise, use a formula such as the following:

1TODAY() - 30 > 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 five-day work week, and then the difference between them is taken to calculate weekdays.

1(5 * ( FLOOR( ( date_1 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_1 - DATE( 1900, 1, 8), 7 ) ) )
2-
3(5 * ( FLOOR( ( date_2 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_2 - DATE( 1900, 1, 8), 7 ) ) )

In this formula, date_1 is the more recent date and date_2 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 12. 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.

1((YEAR(date_1) - YEAR(date_2))*12) + (MONTH(date_1) - MONTH(date_2))

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 5 days to a date, the formula is date + 5.

If you want to add a certain number of months to a date, use this function.

1ADDMONTHS()

For example, if you want to add 4 months to a date, use this formula.

1ADDMONTHS(date + 4)

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.

1ADDMONTHS(date, 12*num_years)

If the date that you provide is February 29, and the resulting year isn’t a leap year, the formula returns the date as February 28. In this scenario, if you want the resulting date as March 1, use this formula.

1IF( MOD((Year (ADDMONTHS(date, 12* num_years)-1960),4)=0, ADDMONTHS(date,12* num_years)+1,ADDMONTHS(date, 12*num_years))

Add Business Days to a Date

This formula finds three business days from a given date.

1CASE( 
2  MOD( date - DATE( 1900, 1, 7 ), 7 ),
3  3, date + 2 + 3,
4  4, date + 2 + 3,
5  5, date + 2 + 3,
6  6, date + 1 + 3,
7  date + 3
8)

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 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 Date/Time

To get the hour, minute, and second from a Date/Time field as a numerical value, use the following formulas where TZoffset is the difference between the user’s time zone and GMT. For hour in 24–hour format:

1VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) )

For hour in 12–hour format:

1IF(
2  OR(
3    VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 0,
4    VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 12
5  ),
6  12,
7  VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) 
8   - 
9   IF( 
10     VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12, 
11     0, 
12     12 
13   )
14)

For minutes:

1VALUE( MID( TEXT( date/time - TZoffset ), 15, 2 ) )

For seconds:

1VALUE( MID( TEXT( date/time - TZoffset ), 18, 2 ) )

And, to get “AM” or “PM” as a string, use:

1IF(
2  VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12,
3  "AM",
4  "PM"
5)

To return the time as a string in “HH:MM:SS A/PM” format, use the following formula:

1IF(
2  OR(
3    VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 0,
4    VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 12
5  ),
6  "12",
7  TEXT( VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) 
8   -
9   IF( 
10     VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12, 
11     0, 
12     12 
13   ) 
14  )
15)
16& ":" &
17MID( TEXT( date/time - TZoffset ), 15, 2 )
18& ":" &
19MID( TEXT( date/time - TZoffset ), 18, 2 )
20& " " &
21IF(
22  VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12,
23  "AM",
24  "PM"
25)

When working with time in formula fields, always consider the time difference between your organization and GMT. See A Note About Date/Time and Time Zones for more information about the time zone offset used in this formula.

Find the Elapsed Time Between Date/Times

To find the difference between two Date values as a number, subtract one from the other like so: date_1 — date_2 to return the difference in days.

Finding the elapsed time between two Date/Time values is slightly more complex. This formula converts the difference between two Date/Time values, datetime_1 and datetime_2, to days, hours, and minutes.

1IF(
2  datetime_1 - datetime_2 > 0 ,
3  TEXT( FLOOR( datetime_1 - datetime_2 ) ) & " days "
4  & TEXT( FLOOR( MOD( (datetime_1 - datetime_2 ) * 24, 24 ) ) ) & " hours "
5  & TEXT( ROUND( MOD( (datetime_1 - datetime_2 ) * 24 * 60, 60 ), 0 ) ) & " minutes",
6  ""
7)

Find the Number of Business Hours Between Two Date/Times

The formula to find business hours between two Date/Time values expands on the formula to find elapsed business days. It works on the same principle of using a reference Date/Time. In this case 1/8/1900 at 16:00 GMT, or 9:00 AM PDT, and then finding your Dates’ respective distances from that reference. The formula rounds the value it finds to the nearest hour and assumes an 8–hour, 9:00 AM5:00 PM work day.

1ROUND( 8 * (
2   ( 5 * FLOOR( ( NOW() - DATETIMEVALUE( '1900-01-08 16:00:00') ) / 7) +
3    MIN(5, 
4     FLOOR ( MOD ( NOW() - DATETIMEVALUE( '1900-01-08 16:00:00'), 7) / 1) +
5     MIN( 1, 24 / 8 * ( MOD( NOW () - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )
6    ) 
7   )
8 -
9   ( 5 * FLOOR( ( MQL_datetime_c - DATETIMEVALUE( '1900-01-08 16:00:00') ) / 7) +
10     MIN( 5,
11      FLOOR( MOD( MQL_datetime_c - DATETIMEVALUE( '1900-01-08 16:00:00'), 7) / 1) +
12      MIN( 1, 24 / 8 * ( MOD( MQL_datetime_c - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )
13    )
14   ) 
15  ), 
162 )

You can change the eights in the formula to account for a longer or shorter work day. If you live in a different time zone or your work day doesn’t start at 9:00 AM, change the reference time to the start of your work day in GMT. See A Note About Date/Time and Time Zones for more information.

Examples of Advanced Formula Fields