Article 004
Salesforce uses some out of box things to provide us better experience. So formula field is one of the very good concept comes from salesforce. Nowadays many small companies are depending on salesforce technology for their profit.
Date formulas are useful for managing payment deadlines, contract ages, or any other features of your organization that are time or date dependent. Two data types are used for working with dates, they are
-
Date
-
Date/Time
Some of the Date functions in formula are as follows
1. Exclude Weekends from Calculating Due Date
Calculate Due Date from Start Date and Number Of Business Days
Due Date = Start Date + Number of Days
Number of Days should be excluded from Weekends(Saturdays and Sundays)
Fields are,
Due_Date__c,
Start_Date__c,
Number_of_Days__c.
Due_Date__c = Start_Date__c + Number_of_Days__c.
so the logic for calculating Due_Date__c without weekends is,
Due_Date__c =
2. Calculate Age from Date of Birth
Fields are,
Date_of_Birth__c
3. Display Weekend Date from Given Date
Calculate the weekend date from given date.
if Given date is,
Date__c = 12/23/2015 (Wednesday)
then weekend should be,
Weekend__c = 12/26/2015 (saturday is the weekend starting day)
So Weekend__c =
Note : Note that if a record is created on a Saturday, this will calculate the following Saturday. If you'd like to change that and return the same date you can just remove the '+7' from the first statement.
4. Formate a Day as 23rd December 2015 from 12/23/2015
Convert date format mm/dd/yyyy to dd(postfix) month year
Fields are,
Date_of_Birth__c = 12/23/2015
so, result would be,
Converted format = 23rd December 2015
Converted_format__c =
CASE(
MOD(Start_Date__c - DATE(1900, 1, 7), 7), 0, (Start_Date__c) +
Number_of_Days__c + FLOOR((Number_of_Days__c-1)/5)*2, 1, (Start_Date__c) +
Number_of_Days__c + FLOOR((Number_of_Days__c)/5)*2, 2, (Start_Date__c) +
Number_of_Days__c + FLOOR((Number_of_Days__c+1)/5)*2, 3, (Start_Date__c) +
Number_of_Days__c + FLOOR((Number_of_Days__c+2)/5)*2, 4, (Start_Date__c) +
Number_of_Days__c + FLOOR((Number_of_Days__c+3)/5)*2, 5, (Start_Date__c) +
Number_of_Days__c + CEILING((Number_of_Days__c)/5)*2, 6, (Start_Date__c) - IF(Number_of_Days__c>0,1,0) + Number_of_Days__c + CEILING((Number_of_Days__c)/5)*2,
null)
Way 1 : IF(MONTH(TODAY())>MONTH(Date_of_Birth__c),YEAR(TODAY())YEAR(Date_of_Birth__c), IF(AND(MONTH(TODAY())=MONTH(Date_of_Birth__c),DAY(TODAY())>=DAY(Date_of_Birth__c)), YEAR(TODAY())-YEAR(Date_of_Birth__c),(YEAR(TODAY())-YEAR(Date_of_Birth__c))-1))
Way 2 : FLOOR((TODAY() - Date_of_Birth__c)/365.2425)
Way 3 : TEXT(YEAR(TODAY() ) - YEAR( Date_of_Birth__c))
CASE(
MOD(field A- DATE(1900, 1, 6),7),
0, field A + 7,
1, field A + 6,
2, field A + 5,
3, field A + 4,
4, field A + 3,
5, field A + 2,
6, field A + 1,
NULL )
IF(
ISBLANK( Date_Of_Birth__c ),
NULL, (
TEXT(DAY(Date_Of_Birth__c ))&
CASE( DAY(Date_Of_Birth__c ),
1, 'st',
2, 'nd',
3, 'rd',
21,'st',
22,'nd',
23,'rd',
31,'st',
'th' )&' '&
CASE( MONTH( Date_Of_Birth__c ),
1, "January",
2, "February",
3, "March",
4, "April",
5, "May",
6, "June",
7, "July",
8, "August",
9, "September",
10, "October",
11, "November",
"December" )&','&
TEXT(YEAR(Date_Of_Birth__c )) ) )