Kyle Smith

Reference: Calculated Field formulas!

Discussion created by Kyle Smith on Jun 21, 2017
Latest reply on Jul 6, 2017 by Alex Nassi

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.  Note: I am condensing the formulas themselves into 1 or 2 lines so this post doesn't take forever to scroll through.

 

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"))))))

Outcomes