Reference: Calculated Field formulas!

Document created by Kyle Smith on Jun 30, 2017Last modified by Kyle Smith on Jul 5, 2017
Version 3Show Document
  • View in full screen mode

I'm converting this post into a compendium of useful calculated field formulas that do not require any code customization to be effective.  Feel free to post some of your own calculated field formulas below (or make the existing ones more efficient) and I'll update the OP.  

 

1) Convert text string to formatted USA Phone Number

It's not pretty, but it'll take up to 17 characters/numbers (even 234///567////8910)and convert it to a standard USA format: +1 (234) 567-8910.  

 

Just copy and paste the below into Notepad, find & replace $phone_work with whatever phone number field you need.  Then put it in a calculated field > formula. 

concat("+1(",subStr(concat(toString(number(subStr($phone_work,0,1))),toString(number(subStr($phone_work,1,1))),toString(number(subStr($phone_work,2,1))),toString(number(subStr($phone_work,3,1))),toString(number(subStr($phone_work,4,1))),toString(number(subStr($phone_work,5,1))),toString(number(subStr($phone_work,6,1))),toString(number(subStr($phone_work,7,1))),toString(number(subStr($phone_work,8,1))),toString(number(subStr($phone_work,9,1))),toString(number(subStr($phone_work,10,1))),toString(number(subStr($phone_work,11,1))),toString(number(subStr($phone_work,12,1))),toString(number(subStr($phone_work,13,1))),toString(number(subStr($phone_work,14,1))),toString(number(subStr($phone_work,15,1))),toString(number(subStr($phone_work,16,1))),toString(number(subStr($phone_work,17,1)))),0,3),") ",subStr(concat(toString(number(subStr($phone_work,0,1))),toString(number(subStr($phone_work,1,1))),toString(number(subStr($phone_work,2,1))),toString(number(subStr($phone_work,3,1))),toString(number(subStr($phone_work,4,1))),toString(number(subStr($phone_work,5,1))),toString(number(subStr($phone_work,6,1))),toString(number(subStr($phone_work,7,1))),toString(number(subStr($phone_work,8,1))),toString(number(subStr($phone_work,9,1))),toString(number(subStr($phone_work,10,1))),toString(number(subStr($phone_work,11,1))),toString(number(subStr($phone_work,12,1))),toString(number(subStr($phone_work,13,1))),toString(number(subStr($phone_work,14,1))),toString(number(subStr($phone_work,15,1))),toString(number(subStr($phone_work,16,1))),toString(number(subStr($phone_work,17,1)))),3,3),"-",subStr(concat(toString(number(subStr($phone_work,0,1))),toString(number(subStr($phone_work,1,1))),toString(number(subStr($phone_work,2,1))),toString(number(subStr($phone_work,3,1))),toString(number(subStr($phone_work,4,1))),toString(number(subStr($phone_work,5,1))),toString(number(subStr($phone_work,6,1))),toString(number(subStr($phone_work,7,1))),toString(number(subStr($phone_work,8,1))),toString(number(subStr($phone_work,9,1))),toString(number(subStr($phone_work,10,1))),toString(number(subStr($phone_work,11,1))),toString(number(subStr($phone_work,12,1))),toString(number(subStr($phone_work,13,1))),toString(number(subStr($phone_work,14,1))),toString(number(subStr($phone_work,15,1))),toString(number(subStr($phone_work,16,1))),toString(number(subStr($phone_work,17,1)))),6,4))

I usually also throw in a dependency: 

greaterThan(strlen($phone_work),6)

This can probably be done cleaner with code customization, but the above works well for OnDemand users that aren't customizing yet.  

 

 

2) Calculating Age (Integer) using a Birthdate

Copy the below into notepad- find and replace $birthdate with the birthdate field of your choice.  

add(subtract(number(subStr(toString(today()),12,4)),number(subStr(toString($birthdate),6,4))),ifElse(greaterThan(monthofyear($birthdate),monthofyear(today())),-1,ifElse(greaterThan(monthofyear(today()),monthofyear($birthdate)),0,ifElse(greaterThan(subtract(number(subStr(toString(today()),5,2)),number(subStr(toString($birthdate),3,2))),-1),0,-1))))

This one was awful to test; toString(today()) gives you like 30 characters including a timestamp, even though it's just supposed to be days.  

 

 

3) Day of the week by name (submitter: Arpad Szabo)

The below formula evaluates a Date field and returns a String of which day of the week that date is on.  Copy the below to notepad, find & replace custom_date_field_c with your date field, then paste into a calculated Text Field.  

ifElse(equal(dayofweek($custom_date_field_c),1),"Monday",ifElse(equal(dayofweek($custom_date_field_c),2),"Tuesday",ifElse(equal(dayofweek($custom_date_field_c),3),"Wednesday",ifElse(equal(dayofweek($custom_date_field_c),4),"Thursday",ifElse(equal(dayofweek($custom_date_field_c),5),"Friday",ifElse(equal(dayofweek($custom_date_field_c),6),"Saturday","Sunday"))))))
4 people found this helpful

Attachments

    Outcomes