Apex Code Development You need to sign in to do that
Don't have an account?
Jeff Sherwood Creating a time zone formula for accounts
Hello,
I'm pretty new to salesforce and formulas so forgive me if I'm missing something obvious. I'm trying to use a formula that will allow me to run reports on accounts sorted by their time zone. This is my formula:
IF(CASE(State, “California”, 1, “Nevada”, 1, “Oregon”, 1, “Washington”, 1, 0) >=1, “Pacific”, null)+
IF(CASE(State, “Arizona”, 1, “Colorado”, 1, “Idaho, 1, “Montana”, 1, “New Mexico”, 1, “Utah”, 1, “Wyoming”, 1, 0) >= 1, “Mountain”, null)+
IF(CASE(State, “Alabama”, 1, “Arkansas”, 1, “Illinois”, 1, “Iowa”, 1, “Kansas”, 1, “Louisiana”, 1, “Minnesota”, 1, “Mississippi”, 1, “Missouri”, 1, “Nebraska”, 1, “North Dakota”, 1, “Oklahoma”, 1, “South Dakota”, 1, “Tennessee”, 1, “Texas”, 1, “Wisconsin”, 1, 0) >= 1, “Central”, null)+
IF(CASE(State, “Connecticut”, 1, “Delaware”, 1, “Florida”, 1, “Georgia”, 1, “Indiana”, 1, “Kentucky”, 1, “Maine”, 1, “Maryland”, 1, “Massachusetts”, 1, “Michigan”, 1, “New Hampshire”, 1, “New Jersey”, 1, “New York”, 1, “North Carolina”, 1, “Ohio”, 1, “Pennsylvania”, 1, “Rhode Island”, 1, “South Carolina”, 1, “Vermont”, 1, “Virginia”, 1, “West Virginia”, 1, 0) >= 1,”Eastern”, null)+
IF(CASE(State, “Alaska”, 1, 0) >=1, “Alaskan”, null)+
IF(CASE(State, “Hawaii”, 1, 0) >=1, “Hawaiian”, null)+
IF(CASE(BillingState, “California”, 1, “Nevada”, 1, “Oregon”, 1, “Washington”, 1, “CA”, 1, “NV”, 1,”OR”, 1, “WA”, 1, 0) >=1, “Pacific”, null)+
IF(CASE(BillingState, “Arizona”, 1, “Colorado”, 1, “Idaho, 1, “Montana”, 1, “New Mexico”, 1, “Utah”, 1, “Wyoming”, 1,“AZ”, 1, “CO”, 1,”ID”, 1, “MT”, 1, “NM”, 1, “UT”, 1, “WY”, 1, 0) >= 1, “Mountain”, null)+
IF(CASE(BillingState, “Alabama”, 1, “Arkansas”, 1, “Illinois”, 1, “Iowa”, 1, “Kansas”, 1, “Louisiana”, 1, “Minnesota”, 1, “Mississippi”, 1, “Missouri”, 1, “Nebraska”, 1, “North Dakota”, 1, “Oklahoma”, 1, “South Dakota”, 1, “Tennessee”, 1, “Texas”, 1, “Wisconsin”, 1, “AL”, 1, “AR”, 1, “IL”, 1, “IA”, 1,”KS”, 1, “LA”, 1,”MN”, 1,”MS”, 1,”MO”, 1,”NE”, 1,”ND”, 1, “OK”, 1,”SD”, 1, “TN”, 1, “TX”, 1,”WI”, 1, 0) >= 1, “Central”, null)+
IF(CASE(BillingState, “Connecticut”, 1, “Delaware”, 1, “Florida”, 1, “Georgia”, 1, “Indiana”, 1, “Kentucky”, 1, “Maine”, 1, “Maryland”, 1, “Massachusetts”, 1, “Michigan”, 1, “New Hampshire”, 1, “New Jersey”, 1, “New York”, 1, “North Carolina”, 1, “Ohio”, 1, “Pennsylvania”, 1, “Rhode Island”, 1, “South Carolina”, 1, “Vermont”, 1, “Virginia”, 1, “West Virginia”, 1, “CT”, 1, “DE”, 1, “FL”, 1,“GA”, 1, “IN”, 1, “KY”, 1,“ME”, 1, “MD”, 1, “MA”, 1,”MI”, 1, “NH”, 1, “NJ”, 1, “NY”, 1, “NC”, 1, “OH”, 1, “PA”, 1, “RI”, 1, “SC”, 1, “VT”, 1, “VA”, 1, “WV”, 1, 0) >= 1,”Eastern”, null)+
IF(CASE(BillingState, “Alaska”, 1,“AK”, 1, 0) >=1, “Alaskan”, null)+
IF(CASE(BillingState, “Hawaii”, 1,“HI”, 1, 0) >=1, “Hawaiian”, null)+
I keep running into a syntax error when I test it. It highlights "California",
Is my formula incorrect? What am I doing incorrectly?
Regards,
Jeff
I'm pretty new to salesforce and formulas so forgive me if I'm missing something obvious. I'm trying to use a formula that will allow me to run reports on accounts sorted by their time zone. This is my formula:
IF(CASE(State, “California”, 1, “Nevada”, 1, “Oregon”, 1, “Washington”, 1, 0) >=1, “Pacific”, null)+
IF(CASE(State, “Arizona”, 1, “Colorado”, 1, “Idaho, 1, “Montana”, 1, “New Mexico”, 1, “Utah”, 1, “Wyoming”, 1, 0) >= 1, “Mountain”, null)+
IF(CASE(State, “Alabama”, 1, “Arkansas”, 1, “Illinois”, 1, “Iowa”, 1, “Kansas”, 1, “Louisiana”, 1, “Minnesota”, 1, “Mississippi”, 1, “Missouri”, 1, “Nebraska”, 1, “North Dakota”, 1, “Oklahoma”, 1, “South Dakota”, 1, “Tennessee”, 1, “Texas”, 1, “Wisconsin”, 1, 0) >= 1, “Central”, null)+
IF(CASE(State, “Connecticut”, 1, “Delaware”, 1, “Florida”, 1, “Georgia”, 1, “Indiana”, 1, “Kentucky”, 1, “Maine”, 1, “Maryland”, 1, “Massachusetts”, 1, “Michigan”, 1, “New Hampshire”, 1, “New Jersey”, 1, “New York”, 1, “North Carolina”, 1, “Ohio”, 1, “Pennsylvania”, 1, “Rhode Island”, 1, “South Carolina”, 1, “Vermont”, 1, “Virginia”, 1, “West Virginia”, 1, 0) >= 1,”Eastern”, null)+
IF(CASE(State, “Alaska”, 1, 0) >=1, “Alaskan”, null)+
IF(CASE(State, “Hawaii”, 1, 0) >=1, “Hawaiian”, null)+
IF(CASE(BillingState, “California”, 1, “Nevada”, 1, “Oregon”, 1, “Washington”, 1, “CA”, 1, “NV”, 1,”OR”, 1, “WA”, 1, 0) >=1, “Pacific”, null)+
IF(CASE(BillingState, “Arizona”, 1, “Colorado”, 1, “Idaho, 1, “Montana”, 1, “New Mexico”, 1, “Utah”, 1, “Wyoming”, 1,“AZ”, 1, “CO”, 1,”ID”, 1, “MT”, 1, “NM”, 1, “UT”, 1, “WY”, 1, 0) >= 1, “Mountain”, null)+
IF(CASE(BillingState, “Alabama”, 1, “Arkansas”, 1, “Illinois”, 1, “Iowa”, 1, “Kansas”, 1, “Louisiana”, 1, “Minnesota”, 1, “Mississippi”, 1, “Missouri”, 1, “Nebraska”, 1, “North Dakota”, 1, “Oklahoma”, 1, “South Dakota”, 1, “Tennessee”, 1, “Texas”, 1, “Wisconsin”, 1, “AL”, 1, “AR”, 1, “IL”, 1, “IA”, 1,”KS”, 1, “LA”, 1,”MN”, 1,”MS”, 1,”MO”, 1,”NE”, 1,”ND”, 1, “OK”, 1,”SD”, 1, “TN”, 1, “TX”, 1,”WI”, 1, 0) >= 1, “Central”, null)+
IF(CASE(BillingState, “Connecticut”, 1, “Delaware”, 1, “Florida”, 1, “Georgia”, 1, “Indiana”, 1, “Kentucky”, 1, “Maine”, 1, “Maryland”, 1, “Massachusetts”, 1, “Michigan”, 1, “New Hampshire”, 1, “New Jersey”, 1, “New York”, 1, “North Carolina”, 1, “Ohio”, 1, “Pennsylvania”, 1, “Rhode Island”, 1, “South Carolina”, 1, “Vermont”, 1, “Virginia”, 1, “West Virginia”, 1, “CT”, 1, “DE”, 1, “FL”, 1,“GA”, 1, “IN”, 1, “KY”, 1,“ME”, 1, “MD”, 1, “MA”, 1,”MI”, 1, “NH”, 1, “NJ”, 1, “NY”, 1, “NC”, 1, “OH”, 1, “PA”, 1, “RI”, 1, “SC”, 1, “VT”, 1, “VA”, 1, “WV”, 1, 0) >= 1,”Eastern”, null)+
IF(CASE(BillingState, “Alaska”, 1,“AK”, 1, 0) >=1, “Alaskan”, null)+
IF(CASE(BillingState, “Hawaii”, 1,“HI”, 1, 0) >=1, “Hawaiian”, null)+
I keep running into a syntax error when I test it. It highlights "California",
Is my formula incorrect? What am I doing incorrectly?
Regards,
Jeff
The quotes look different(in your formula the quotes are slanted while when adding from the formula editor quotes are straight). Please delete all the quotes and add them again in the formula editor itself. I tried and it worked.
Thanks
"Louisiana", 1, “Minnesota” ---- The first quotes work but the second throws syntax error.
http://www.craigrjordan.com/salesforce/create-a-timezone-formula-in-salesforce/
IF(CASE(BillingState, "CA", 1, "NV", 1,"OR", 1, "WA", 1, 0) >=1, "Pacific", null)+
IF(CASE(BillingState, "AZ", 1, "CO", 1,"ID", 1, "MT", 1, "NM", 1, "UT", 1, "WY", 1, 0) >= 1, "Mountain", null)+
IF(CASE(BillingState, "AL", 1, "AR", 1, "IL", 1, "IA", 1,"KS", 1, "LA", 1,"MN", 1,"MS", 1,"MO", 1,"NE", 1,"ND", 1, "OK", 1,"SD", 1,"WI", 1, 0) >= 1, "Central", null)+
IF(CASE(BillingState, "CT", 1, "DE", 1, "GA", 1, "ME", 1, "MD", 1, "MA", 1,"MI", 1, "NH", 1, "NJ", 1, "NY", 1, "NC", 1, "OH", 1, "PA", 1, "RI", 1, "SC", 1, "VT", 1, "VA", 1, "WV", 1, 0) >= 1,"Eastern", null)+
IF(CASE(BillingState, "AK", 1, 0) >=1, "Alaskan", null)+
IF(CASE(BillingState, "HI", 1, 0) >=1, "Hawaiian", null)+
IF(BillingState = "FL", IF(MID(Phone,1,3) = "850", "Central", "Eastern"),null)+
IF(BillingState = "IN", IF(MID(Phone,1,3) = "219", "Central", "Eastern"),null)+
IF(BillingState = "KY", IF(MID(Phone,1,3) = "270", "Central", "Eastern"),null)+
IF(BillingState = "TX", IF(MID(Phone,1,3) = "915", "Mountain", "Central"),null)+
IF(BillingState = "TN", IF(CASE(MID(Phone,1,3),"865",1,"423",1,0)>=1, "Eastern", "Central"),null)
You'll notice the last few statements refer to phone numbers with area codes of states with different time zones, for example Kentucky with a 270 area code, that representing the Western part of the state which in the central time zone. The rest are in the East.
I trust this helps.
Thanks