Skip to main content
Topics#Data ManagementPregunta de Bridget Hunt
Bridget Hunt (Merchants Bonding Company) ha preguntado en #Data Management
29 sept 2016, 16:40

Date formula with static date until date arrives.

I created a formula date field to fill in a specific date dependent upon the shipping state. All the dates need to be the current year except Texas, which needs to say 1/14/2018 until that date, and then needs to continue updating with the current year. This is what I have currently, but it doesn't fulfill the Texas exception.

 

IF(CONTAINS("AR:GA:MS:NJ:PA:SD:UT:VT:WV", ShippingState),DATE(YEAR(TODAY()),2,14), 

 

IF(CONTAINS("CT:NE", ShippingState),DATE(YEAR(TODAY()),3,14), 

 

IF(CONTAINS("NV:VA:WA", ShippingState),DATE(YEAR(TODAY()),4,14), 

 

IF(CONTAINS("OH:MA", ShippingState),DATE(YEAR(TODAY()),5,14), 

 

IF(CONTAINS("AK:AZ:CO:IL:IN:MD:MO:OR:RI", ShippingState),DATE(YEAR(TODAY()),6,14), 

 

IF(CONTAINS("ME:OK", ShippingState),DATE(YEAR(TODAY()),8,14), 

 

IF(CONTAINS("AL:IA:KS:KY:MI:NC", ShippingState),DATE(YEAR(TODAY()),9,14), 

 

IF(CONTAINS("LA:WI:WY", ShippingState),DATE(YEAR(TODAY()),11,14), 

 

IF(CONTAINS("DC:ND:NM", ShippingState),DATE(YEAR(TODAY()),12,14), 

 

IF(CONTAINS("TX", ShippingState),DATE(YEAR(TODAY()),1,14), 

 

DATE(YEAR(TODAY()),7,14)))))))))))
5 respuestas
  1. Bridget Hunt (Merchants Bonding Company)
    29 sept 2016, 17:45
    It's saying I'm still 52 characters too long - I guess because of the second formula itself? I'm not totally clear on the whole character issue; this is the first time it's come up for me. I tried changing it to a case statement instead of if, and that seems to be working for all except Texas, where I am seeing #.Error! in the field on the account pages. Can you help fix that or is case not a good option?

     

    CASE( ShippingState , 

     

    "AR:GA:MS:NJ:PA:SD:UT:VT:WV",DATE(YEAR(TODAY()),2,14), 

     

    "CT:NE",DATE(YEAR(TODAY()),3,14), 

     

    "NV:VA:WA",DATE(YEAR(TODAY()),4,14), 

     

    "OH:MA",DATE(YEAR(TODAY()),5,14), 

     

    "AK:AZ:CO:IL:IN:MD:MO:OR:RI", DATE(YEAR(TODAY()),6,14), 

     

    "ME:OK", DATE(YEAR(TODAY()),8,14), 

     

    "AL:IA:KS:KY:MI:NC", DATE(YEAR(TODAY()),9,14), 

     

    "LA:WI:WY",DATE(YEAR(TODAY()),11,14), 

     

    "DC:ND:NM",DATE(YEAR(TODAY()),12,14), 

     

    "TX",IF( TODAY() < DATE(2018,14, 1), DATE(2018,1,14), DATE(YEAR(TODAY()),1,14) ), 

     

    DATE(YEAR(TODAY()),7,14))
Escribir una respuesta...
0/9000