Salesforce: Useful Formulas related to Date

Here are some useful formula related to date. In this sample, I have a custom field call Execution Time with data type = Date/Time

Find me Execution Date
Create a formula field with return type = Date --> Execution_Date__c
DATEVALUE(Execution_Time__c)


Find me last date of the month of Execution Date
Create a formula field with return type = Date
CASE( 
MONTH(Execution_Date__c), 
1, DATE( YEAR( Execution_Date__c ), 01, 31), 
2, DATE( YEAR( Execution_Date__c ), 02, IF( MOD( YEAR(Execution_Date__c), 4) = 0, 29, 28)), 
3, DATE( YEAR( Execution_Date__c ), 03, 31), 
4, DATE( YEAR( Execution_Date__c ), 04, 30), 
5, DATE( YEAR( Execution_Date__c ), 05, 31), 
6, DATE( YEAR( Execution_Date__c ), 06, 30), 
7, DATE( YEAR( Execution_Date__c ), 07, 31), 
8, DATE( YEAR( Execution_Date__c ), 08, 31), 
9, DATE( YEAR( Execution_Date__c ), 09, 30), 
10, DATE( YEAR( Execution_Date__c ), 10, 31), 
11, DATE( YEAR( Execution_Date__c ), 11, 30), 
12, DATE( YEAR( Execution_Date__c ), 12, 31), 
null 
)

This is another simplify formula and will get the same result
IF( 
MONTH( Execution_Date__c ) = 12, 
Date( YEAR( Execution_Date__c ), 12, 31 ), 
Date( YEAR( Execution_Date__c ), MONTH ( Execution_Date__c ) + 1, 1 ) - 1
)


Find me last date next month of Execution Date
Create a formula field with return type = Date
CASE( 
MONTH(Execution_Date__c), 
1, DATE( YEAR( Execution_Date__c ), 02, IF( MOD( YEAR(Execution_Date__c), 4) = 0, 29, 28)), 
2, DATE( YEAR( Execution_Date__c ), 03, 31), 
3, DATE( YEAR( Execution_Date__c ), 04, 30), 
4, DATE( YEAR( Execution_Date__c ), 05, 31), 
5, DATE( YEAR( Execution_Date__c ), 06, 30), 
6, DATE( YEAR( Execution_Date__c ), 07, 31), 
7, DATE( YEAR( Execution_Date__c ), 08, 31), 
8, DATE( YEAR( Execution_Date__c ), 09, 30), 
9, DATE( YEAR( Execution_Date__c ), 10, 31), 
10, DATE( YEAR( Execution_Date__c ), 11, 30), 
11, DATE( YEAR( Execution_Date__c ), 12, 31), 
12, DATE( YEAR( Execution_Date__c )+1, 01, 31),
null 
)


Find me calendar week of Execution Date
Create a formula field with return type = Number
CEILING(((DATEVALUE(Execution_Time__c) - DATE(YEAR(DATEVALUE(Execution_Time__c)), 1, 1) + 1) + MOD(DATE(YEAR(DATEVALUE(Execution_Time__c)), 1, 1) - DATE(1900, 1, 7), 7)) / 7) - 1 

Another option:

CEILING((DATEVALUE(Execution_Time__c) - DATE(YEAR(DATEVALUE(Execution_Time__c)), 1, 4) +1 ) / 7)

*** to confirm week is correct, use this website http://www.calendar-365.com

*** 1 Jan 1900 = Monday

Find me day of the week from Execution Date
Create a formula field with return type = Text

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

*** 7 Jan 1900 = Sunday


Find me the day of Execution Date

Create a formula field with return type = Number

DAY(Execution_Date__c)

Find me number of day between Request DateTime with Execution Date Time

Create a formula field with return type = Number

DATEVALUE(Execution_Time__c) - DATEVALUE(Request_Time__c)


Find me first date of the week of record Creation Date

Create a formula field with return type = Date

DATEVALUE(CreatedDate) - MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7)
This formula will return Sunday as the first date of the week.

Find me first date of the week of record Creation Date, with MONDAY as the first date of the week

Create a formula field with return type = Date

CASE( MOD( DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7), 0, 
DATEVALUE(CreatedDate) - MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7) - 6, 
DATEVALUE(CreatedDate) - MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7) + 1)
*** Case result 0 = Sunday


Reference: 

• Common Date Formulas
• Sample Date Validation Rules

Last update: 20 Jun 2016