Convenient date-related formulas in Salesforce

Obtaining the Current Date

The current date can be obtained using the function.

TODAY()

Adding to or subtracting from date

Date data type additions and subtractions are in day units.

If you wish to obtain a date days later, add days.

Date data type + n

Additions and subtractions in month units use the function.

ADDMONTHS (Date data type , n )

Additions and subtractions in year units are made using additions and subtractions in month units multiplied by .

ADDMONTHS (Date data type , n * 12 )

Calculating year, month and day from the date

The function, function and function are used to calculate the year, month and day from the date.

The following format is used to obtain the year.

YEAR (Date data type)

The following format is used to obtain the month.

MONTH (Date data type)

The following format is used to obtain the day.

DAY (Date data type)

Calculating the date from the year, month and day

The function or the function is used to obtain the date from the year, month and day.

The passes the year, month and day as a number.

DATE( YYYY , MM , DD )

The function passes the date as a string.

DATEVALUE( "YYYY-MM-DD" )

Calculating the first day of any month from the date

The first day of the month can be obtained using the function and the function because the function uses the year/month/day number as an argument to change it to theDate data type.

DATE( YEAR (Date data type) , MONTH (Date data type) , 1 )

The function is used to add in month units to calculate the first day months later.

ADDMONTHS( DATE( YEAR (Date data type) , MONTH (Date data type) , 1 ) , n )

Because the function also supports negative values, the same formula can be used to calculate the first day of months earlier.

Calculating the last day of any month from the date

The last day of the month, such as the or st cannot be used in the function because, unlike the first day, the day is not constant.

The calculation is performed by subtracting one day from the first day of the following month.

ADDMONTHS( DATE( YEAR (Date data type) , MONTH (Date data type) , 1 ) , 1 ) - 1

Like the first day, months later or months earlier can be adjusted using the function.

ADDMONTHS( DATE( YEAR (Date data type) , MONTH (Date data type) , 1 ) , n + 1 ) - 1

Calculating day of the week from the date

The function can be used to calculate the day of the week from the date.

WEEKDAY (Date data type)

Because the function expresses the day of the week as a number, it can be clearly written by separating it with the function if you want it in text.

CASE( WEEKDAY (Date data type) ,
  1 , "Sunday" ,
  2 , "Monday" ,
  3 , "Tuesday" ,
  4 , "Wednesday" ,
  5 , "Thursday" ,
  6 , "Friday" ,
  7 , "Saturday" ,
  "-"
)

Calculating the number of days from a date until today

The number of days between dates can be calculated using a difference calculation.

TODAY() -Date data type

Calculating the number of months from a date until today

The year and month can be converted into the number of months to calculate the number of months between dates.

( YEAR( TODAY() ) - YEAR (Date data type) ) * 12 + ( MONTH( TODAY() ) - MONTH (Date data type) )

This enables simple calculation of the number of months, but differences may occur in values when dates fall within the same month.

It is necessary incorporate a check on the relative number of days, due to differences that can occur when the number of months changes depending on the number of days (e.g., in age calculations).

( YEAR( TODAY() ) - YEAR (Date data type) ) * 12 + ( MONTH( TODAY() ) - MONTH (Date data type) )
- IF( AND( MONTH( TODAY() ) = MONTH (Date data type) , DAY( TODAY() ) < DAY (Date data type) ) , 1 , 0 )

`AND( MONTH( TODAY() ) = MONTH( ) , DAY( TODAY() ) < DAY( ) )` `FALSE`

( YEAR( TODAY() - DAY (Date data type) + 1 ) - YEAR (Date data type) ) * 12
+ ( MONTH( TODAY() - DAY (Date data type) + 1 ) - MONTH (Date data type) )

Calculating the number of years from a date until today

Unlike the number of months, the number of years between dates can be calculated using a simple quotient of coefficients.

ROUND( ( TODAY() -Date data type) / 365.2425 , 0 )

seems superfluous, but it is the extra amount needed adjust for leap years.

Leap year conditions

Western calendar years can be divided by

As an exception for (1), years that can be divided by and not divided by are treated as normal years

Simply put, “ of the years in any consecutive years are leap years.” Therefore, when thinking of the number of days over years, it is .

Averaging this over years, year is days.