Sample Date Formulas | Salesforce
## Sample Date Formulas
Watch a Demo: How to Calculate the Number of Days a Case is Open ## Finding the Day, Month, or Year from a DateUse the functions MONTH( , and date )YEAR( to return their respective numerical values. Replace date ) with a value of type Date (e.g. dateTODAY()). To use these functions with Date/Time values, first convert them to a date with the ## Finding Out if a Year Is a Leap YearThis formula determines whether or not 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.
OR( MOD( YEAR( ## Finding Which Quarter a Date Is InFor standard quarters, you can determine which quarter a date falls in using this formula. This formula returns the number of the quarter in which CEILING( MONTH ( CEILING( ( MONTH ( TODAY()’s year and quarter.AND( CEILING( MONTH( ## Finding the Week of the Year a Date Is InTo find the number of a date’s week of the year, use this formula:
IF( CEILING( ( 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 week of January.## Finding Whether Two Dates Are in the Same MonthTo 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( ## Finding the Last Day of the MonthThe 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( ## Displaying the Month as a String Instead of a NumberTo return the month as a text string instead of a number, use:
CASE( MONTH( CASE( MONTH( ## Finding and Displaying the Day of the Week From a DateTo find the day of the week from a Date value, use a known Sunday (e.g. January 7, 1900) and subtract it from the date (e.g.
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). The formula below finds the result and then returns the text name of that day.CASE( MOD( 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 8, 1900 in your condition. The new formula looks like this:
CASE( MOD( 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 $Label.Day_of_Week_1, etc. ## Finding the Next Day of the Week After a DateTo 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
and a date, a number 0–6 where 0 = Sunday and 6 = Saturday. By adding this difference to the current date, you’ll find the date of the day_of_week. The day_of_weekIF() statement in this formula handles cases where the is prior to the day of the week of the day_of_week value (e.g. date is a Thursday and date is a Monday) by adding 7 to the difference.day_of_week
value based on your needs.day_of_week## Finding the Number of Days Between Two DatesTo find the number of days between two dates, , subtract the earlier date from the later date: date_2date_1 — date_2You 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 30 days prior to the current date and false otherwise. This formula does just that:
TODAY() - 30 > ## Finding the Number of Business Days Between Two DatesCalculating 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 you’re examining. These values are multiplied by five (for a five-day work week) and then the difference between them is taken to calculate business days.
(5 * ( FLOOR( ( is the more recent date and date_1 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.date_2## Adding Days, Months, and Years to a DateIf 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 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 29 (a leap year) results in an invalid date. The following formula adds
to num_years by checking if the date is February 29 and if the future date is not in a leap year. If these conditions hold true, the formula returns March 1 in the future year. Otherwise, the formula sets the Date to the same month and day date in the future.num_yearsIF( AND( MONTH(
This formula does the following:
- Returns March 1 if the future month is a February and the day is greater than 28. This portion of the formula performs the same for both leap and non-leap 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 30.
- Otherwise, it returns the correct date in the future month.
DATE( YEAR( before the calculated date.## Adding Business Days to a DateThis formula finds three business days from a given
.dateCASE( MOD( 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.date## Finding the Hour, Minute, or Second from a Date/TimeTo get the hour, minute, and second from a Date/Time field as a numerical value, use the following formulas where
is the difference between the user’s time zone and GMT. For hour in 24–hour format:TZoffsetVALUE( MID( TEXT( IF( OR( VALUE( MID( TEXT( VALUE( MID( TEXT( VALUE( MID( TEXT( IF( VALUE( MID( TEXT( IF( OR( VALUE( MID( TEXT( ## Finding the Elapsed Time Between Date/TimesTo find the difference between two Date values as a number, subtract one from the other like so: Finding the elapsed time between two Date/Time values is slightly more complex. This formula converts the difference between two Date/Time values,
and datetime_1, to days, hours, and minutes.datetime_2IF( ## Finding the Number of Business Hours Between Two Date/TimesThe formula for finding business hours between two Date/Time values expands on the formula for finding 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 (9 a.m. 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 a.m. – 5 p.m. work day.
ROUND( 8 * ( ( 5 * FLOOR( ( DATEVALUE( |