You need to sign in to do that
Don't have an account?

TimeZone Formula field
Helo,
I'm trying to figure out why the below formula is only populating some records and not all. Can some one please assist?
IF(CASE(ShippingState, 'BC', 1, 'CA', 1, 'NV', 1,'OR', 1, 'WA', 1, 'YT', 1, 0) >=1, "Pacific (PST)", null)+
IF(CASE(ShippingState, 'AB', 1, 'AZ', 1, 'CO', 1,'ID', 1, 'MT', 1, 'NM', 1, 'UT', 1, 'WY', 1, 0) >= 1, "Mountain (MST)", null)+
IF(CASE(ShippingState, 'AL', 1, 'AR', 1, 'IL', 1, 'IA', 1,'KS', 1, 'LA', 1, 'MB', 1, 'MN', 1,'MS', 1,'MO', 1,'NE', 1,'ND', 1, 'OK', 1,'SD', 1, 'SK', 1, 'WI', 1, 0) >= 1, "Central (CST)", null)+
IF(CASE(ShippingState, 'CT', 1, 'DE', 1, 'GA', 1, 'ME', 1, 'MD', 1, 'MA', 1,'MI', 1, 'NH', 1, 'NJ', 1, 'NY', 1, 'NC', 1, 'OH', 1, 'ON', 1, 'PA', 1, 'QC', 1, 'RI', 1, 'SC', 1, 'VT', 1, 'VA', 1, 'WV', 1, 0) >= 1, "Eastern (EST)", null)+
IF(CASE(ShippingState, 'AK', 1, 0) >=1, "Alaskan (AKST)", null)+
IF(CASE(ShippingState, 'HI', 1, 0) >=1, "Hawaiian (HAST)", null)+
IF(ShippingState = 'FL', IF(MID(Phone,2,3) = "850","Central (CST)","Eastern (EST)"),null)+
IF(ShippingState = 'IN', IF(MID(Phone,2,3) = "219","Central (CST)","Eastern (EST)"),null)+
IF(ShippingState = 'KY', IF(MID(Phone,2,3) = "270","Central (CST)","Eastern (EST)"),null)+
IF(ShippingState = 'TX', IF(MID(Phone,2,3) = "915","Mountain (MST)","Central (CST)"),null)+
IF(ShippingState = 'TN', IF(CASE(MID(Phone,2,3),"865",1,"423",1,0)>=1,"Eastern (EST)", "Central (CST)"),null)
I'm trying to figure out why the below formula is only populating some records and not all. Can some one please assist?
IF(CASE(ShippingState, 'BC', 1, 'CA', 1, 'NV', 1,'OR', 1, 'WA', 1, 'YT', 1, 0) >=1, "Pacific (PST)", null)+
IF(CASE(ShippingState, 'AB', 1, 'AZ', 1, 'CO', 1,'ID', 1, 'MT', 1, 'NM', 1, 'UT', 1, 'WY', 1, 0) >= 1, "Mountain (MST)", null)+
IF(CASE(ShippingState, 'AL', 1, 'AR', 1, 'IL', 1, 'IA', 1,'KS', 1, 'LA', 1, 'MB', 1, 'MN', 1,'MS', 1,'MO', 1,'NE', 1,'ND', 1, 'OK', 1,'SD', 1, 'SK', 1, 'WI', 1, 0) >= 1, "Central (CST)", null)+
IF(CASE(ShippingState, 'CT', 1, 'DE', 1, 'GA', 1, 'ME', 1, 'MD', 1, 'MA', 1,'MI', 1, 'NH', 1, 'NJ', 1, 'NY', 1, 'NC', 1, 'OH', 1, 'ON', 1, 'PA', 1, 'QC', 1, 'RI', 1, 'SC', 1, 'VT', 1, 'VA', 1, 'WV', 1, 0) >= 1, "Eastern (EST)", null)+
IF(CASE(ShippingState, 'AK', 1, 0) >=1, "Alaskan (AKST)", null)+
IF(CASE(ShippingState, 'HI', 1, 0) >=1, "Hawaiian (HAST)", null)+
IF(ShippingState = 'FL', IF(MID(Phone,2,3) = "850","Central (CST)","Eastern (EST)"),null)+
IF(ShippingState = 'IN', IF(MID(Phone,2,3) = "219","Central (CST)","Eastern (EST)"),null)+
IF(ShippingState = 'KY', IF(MID(Phone,2,3) = "270","Central (CST)","Eastern (EST)"),null)+
IF(ShippingState = 'TX', IF(MID(Phone,2,3) = "915","Mountain (MST)","Central (CST)"),null)+
IF(ShippingState = 'TN', IF(CASE(MID(Phone,2,3),"865",1,"423",1,0)>=1,"Eastern (EST)", "Central (CST)"),null)
Thanks,
Parker
Thanks,
Parker
If you run into compile size errors, you might be able to create a helper formula. It would work like this:
Here's a few options for you, if any of them help, please let me know.
Thanks,
Parker
Any suggestions.
'TN', CASE(MailingPostalCode, 'Code1', 4, 'Code2' , 4, /*More Codes*/ 3) However, I don't know if that will be practical, or even if that will be a 100% confusion free and accurate solution. I think we're running into another problem with the method of implementation again, but I think this time the formula will need to be changed, as not all of your clients in Tennessee will have the phone number with an 865 or 423 area code. Let me know and/or mark a best answer if this helps.
IF(CONTAINS("FL,DC,GA,SC,NC,VA,WV,PA,MD,DE,CT,NY,NJ,ME,MA",State), "Eastern", IF(CONTAINS("AL,AR,IL,IN,IA,KS,KY,LA,MI,MN,MS,MO,NE,ND,OK,SD,TN,TX,WI",State), "Central", IF(CONTAINS("AZ,CO,ID,MT,NM,UT,WY",State), "Mountain", IF(CONTAINS("CA,WA,OR,NV",State), "Pacific", "Other"))))