Pages

Sunday, January 6, 2013

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 
)

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


Last update: 20 Jun 2016