top of page
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 

 

  1.  Date

  2.  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 )) ) )

bottom of page