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
| Name | Description |
| Today() | Returns the current day, month, and year as a Date data type |
| Now() | Returns the Date/Time value of the current moment |
| Date() | Returns a Date value, given a year, month, and day. Numerical Y/M/D values and the YEAR(), MONTH(), and DAY() functions are valid parameters for DATE() |
| DateValue() | DATEVALUE( date/time ) function to return the Date value of a Date/Time |
| DateTimeValue() | Converts a Date value to a Date/Time |
1. Exclude Weekends from Calculating Due Date
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 =
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)
2. Calculate Age from Date of Birth
Fields are,
Date_of_Birth__c
Fields are,
Date_of_Birth__c
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))
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 =
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
)
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 =
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 ))
)
)
Hope these functions are helpful. Thanks!.
No comments:
Post a Comment