Change my code into Excel formula code

I have never written an advanced formula in excel before and am looking into making a Logical:IF statement/formula but I don't know how. I took some classes in programming 10+ years ago so I can follow the logic somewhat...

I want to write a formula that will tell me how many days it will take the warehouse to deliver to the ship-to state.

Column A I have which Warehouse it is shipping from= "East"(NC) or "West"(CA)
Column B is the state it is shipping to.
Column C is blank but I want it to calculate the days it will take to ship with this formula/macro.

Here is my try at the coding: Can someone help me put it into Excel coding?(== = equal, || = or, // = comment)

IF(A1 == "EAST" && B1 == CA || NV || WY || OR || WA || MT || UT || ID,
then C1 == "5 Days";
ELSEIF(A1 == "EAST" && B1 == ND || SD || NE || CO ||AZ || NM,
then C1 == "4 Days";
ELSEIF(A1 == "EAST" && B1 == KS || IA || MN || WI || OK || AR || TX || MA || RI || ME || NH || VT,
then C1 == "3 Days";
ELSEIF(A1 == "EAST" && B1 == GA || FL || AL || MS || MO || IN || IL || MI || PA || NY || DC || NJ || CT || MD || WV || KY || OH,
then C1 == "2 Days";
ELSEIF(A1 == "EAST" && B1 == VA || NC || SC,
then C1 == "1 Day";

IF(A1 == "WEST" && B1 == RI || CT || VT || NH || NC || SC || VA,
then C1 == "5 Days";
ELSEIF(A1 == "WEST" && B1 == ND || SD || NE || KS || MN || WI || OH || IA || IL || MO || AR || LA || MS || AL || GA, // ETC.ETC.
then C1 == "4 Days";
ELSEIF(A1 == "WEST" && B1 == WA || ID || OR || MT || CO || NM || OK || TX,
then C1 == "3 Days";
ELSEIF(A1 == "WEST" && B1 == NV || AZ || UT,
then C1 == "2 Days";
ELSEIF(A1 == "WEST" && B1 == CA,
then C1 == "1 Day";


Now there are 5,000+ rows also, so would I just drag the formula down into the rows below it? Or is there a better way to write in the code for next row? So it says (A2 == "WEST" && B2 == CA, then C2 ==)(A3/B3, then C3) etc.etc.

I appreciate the help, this forum always gets the best answers according to google!

DGarret, welcome to the forum

however, please rename your thread to something more meaningful, that actually describes your problem. lots of contributors will look at a thread title, and if it is of interest to them, or falls within their area of expertese, they might only open those threads. also, searching for "help" will not turn up too many results that will benifit you, sorry.
To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title"

"WEST" && B1 == RI || CT || VT || NH || NC || SC || VA,

For B1 I suppose it needs to fit to one of the values (or has it to fit to all the values).

I that case (if you only need one of the values) you can use VLookup for that.

thanks for the title change

perhaps this will get you headed in the right direction (syntax-wise), i assume that this is going into C1 abd being copied down?...

IF(A1 == "EAST" && B1 == CA || NV || WY || OR || WA || MT || UT || ID,
then C1 == "5 Days";

=if(and(A1="east",or(B1="CA",B1="NV",B1="WY",B1="OR",B1="WA",B1="MT",B1="UT",B1="ID")),"5days",if(......

it would be easier if you could put the CA, NV etc in a table and then reference them with ...
ISERROR(VLOOKUP(D3,A3:A10,1,FALSE))

=if(and(A1="east",ISERROR(VLOOKUP(D3,$A$3:$A$10,1,FALSE))),"5days",if(......

and fyi, the restriction of 7 nested if's is no longer valid and you dont need to use double ='s and &'s

I appreciate your help, I re-wrote the code into excel format!

=IF(AND(A1="WEST",OR(B1="RI",B1="CT",B1="VT",B1="NH",B1="NC",B1="SC",B1="VA")),"5 DAYS",IF(AND(A1="WEST",OR(B1="TN",B1="ND",B1="SD",B1="NE",B1="KS",B1="MN",B1="WI",B1="OH",B1="IA",B1="IL",B1="MO",B1="AR",B1="LA",B1="MS",B1="AL",B1="GA",B1="FL",B1="DC",B1="DE",B1="NJ",B1="MA",B1="KY",B1="IN")),"4 DAYS",IF(AND(A1="WEST",OR(B1="OR",B1="WA",B1="ID",B1="MT",B1="WY",B1="CO",B1="NM",B1="OK",B1="TX",B1="AK",B1="HI")),"3 DAYS",IF(AND(A1="WEST",OR(B1="UT",B1="NV",B1="AZ",B1="CO",B1="AZ",B1="NM")),"2 DAYS",IF(AND(A1="WEST",OR(B1="CA")),"1 DAY",IF(AND(A1="EAST",OR(B1="CA",B1="NV",B1="WY",B1="OR",B1="WA",B1="MT",B1="UT",B1="ID")),"5 DAYS",IF(AND(A1="EAST",OR(B1="ND",B1="SD",B1="NE",B1="CO",B1="AZ",B1="NM")),"4 DAYS",IF(AND(A1="EAST",OR(B1="KS",B1="IA",B1="MN",B1="WI",B1="OK",B1="AR",B1="TX",B1="MA",B1="RI",B1="ME",B1="NH",B1="VT")),"3 DAYS",IF(AND(A1="EAST",OR(B1="GA",B1="FL",B1="AL",B1="MS",B1="MO",B1="IN",B1="IL",B1="MI",B1="PA",B1="NY",B1="DC",B1="NJ",B1="CT",B1="MD",B1="WV",B1="KY",B1="OH")),"2 DAYS",IF(AND(A1="EAST",OR(B1="VA",B1="NC",B1="SC")),"1 DAY")))))

However I did get the error "The specified formula cannot be entered because it uses more levels of nesting than allowed in the current file format." with 2010 Microsoft Office Professional Plus Excel.
But if I filter the EAST/WEST column into A-Z, I can split the formula in half for EAST/WEST and it works!

Now I can see why using Lookup with tables would be much easier than searching through my code to make a change, I may make a separate worksheet in the future to reference from.

Another question:
Instead of returning the string "5 days" I would rather it return the value "Customer Request Date minus 5 days", in a date format, but if the date is prior to today I want it to return today's date, and I only want to count Business days(not count Weekends)

Example1: C1(CustomerRequestDate)=01/08/2013 it would return 01/03/2013 (Today's date) Since 01/08/2013 is less than 5 business days away.
Example2: C1(CRD)=02/08/2013 it would return 02/01/2013 Since it is 5 Business days earlier than 02/08/2013
Example3: C1(CRD)=12/17/2012 it would return 01/03/2013 Since it is already past due.

Here’s my try at the code:
I had to change the previous code to return a value instead of a string:
=IF(AND(A1="WEST",OR(B1="RI",B1="CT",B1="VT",B1="NH",B1="NC",B1="SC",B1="VA")),5,IF(…

I was unable to figure out how to fit it into my regular code, so instead I made a new column E1(Ship date)
so A1=Warehouse(West/East), B1=State, C1=CustomerRequestDate, D1=Days to ship, E1=Ship Date, F1=Real Ship Date

=IF(NETWORKDAYS(C1-D1,C1)-1=D1,C1-D1,IF(NETWORKDAYS(C1-D1,C1)-1<D1,C1-D1-2))

//(Comment) If the number of business days of C1(CRD) minus D1(Days it takes to ship) minus 1 day is equal to D1(Days it takes to ship), then there is no weekend and I get the result of C1(CRD) minus D1(Days it takes to ship)
Or else, If the number of business days of C1(CRD) minus D1(Days it takes to ship) minus 1 Day is less than D1(Days it takes to ship), then there is a weekend in there so the result is the C1(CRD) minus D1(DTS) minus 2(weekend)

(I tried another If statement in the previous code to incorporate “but if the date is prior to today I want it to return today's date” but It wasn’t working, so I made another Column:

=IF(E1<=TODAY(),TODAY(),E1)

If you can help me incorporate both codes into one or all three into one I would be appreciated! Thanks for the help, my boss is going to think I am a genius! Lol j/k

not that it really matters now, but if the file format you are using is .xls (2003) then maybe thats why it is restricting you to 7 levels. i have used more and seen far more (1 OP had over 200!!) nested ifs than that, using 2007.

ok, on to your next question i'm trying to figure out what you want, but my brain is lagging a bit (its almost 1am lol) so could you break that down a bit for me please?

This can be reduced to a simple single lookup.

1) Create a WEST lookup table in column F:

WEST
RI CT VT NH NC SC VA
ND SD NE KS MN WI OH IA IL MO AR LA MS AL GA
WA ID OR MT CO NM OK TX
NV AZ UT
CA

2) Add the EAST table in column G:

EAST
CA NV WY OR WA MT UT ID
ND SD NE CO AZ NM
KS IA MN WI OK AR TX MA RI ME NH VT
GA FL AL MS MO IN IL MI PA NY DC NJ CT MD WV KY OH
VA  NC SC

3) Add the "results" table in column H:

RESULTS
5days
4days
3days
2days
1day

4) Now put an EAST/WEST value in A1, and a STATE selection in B1

5) The formula in C1 is now:

=LOOKUP(2, 1/(ISNUMBER(SEARCH(B1, IF(A1="EAST", $G$2:$G$6, $F$2:$F$6)))), $H$2:$H$6)

lol thanks Jerry

I finally got it! Thanks for getting me in the right direction! (syntax-wise)
I did run into the "8192 char limit" error so I split the code up into EAST and WEST.

(WEST ONLY)

=IF(AND(C1-7>TODAY(),NETWORKDAYS(C1-5,C1)=5,A1="WEST",OR(B1="RI",B1="CT",B1="VT",B1="NH",B1="NC",B1="SC",B1="VA",B1="PA",B1="WV",B1="NY",B1="ME")),C1-7,IF(AND(C1-7<=TODAY(),NETWORKDAYS(C1-5,C1)=5,A1="WEST",OR(B1="RI",B1="CT",B1="VT",B1="NH",B1="NC",B1="SC",B1="VA",B1="PA",B1="WV",B1="NY",B1="ME")),TODAY(),IF(AND(C1-7>TODAY(),NETWORKDAYS(C1-5,C1)=4,A1="WEST",OR(B1="RI",B1="CT",B1="VT",B1="NH",B1="NC",B1="SC",B1="VA",B1="PA",B1="WV",B1="NY",B1="ME")),C1-7,IF(AND(C1-7<=TODAY(),NETWORKDAYS(C1-5,C1)=4,A1="WEST",OR(B1="RI",B1="CT",B1="VT",B1="NH",B1="NC",B1="SC",B1="VA",B1="PA",B1="WV",B1="NY",B1="ME")),TODAY(),IF(AND(C1-4>TODAY(),NETWORKDAYS(C1-4,C1)=5,A1="WEST",OR(B1="TN",B1="ND",B1="SD",B1="NE",B1="KS",B1="MN",B1="WI",B1="OH",B1="IA",B1="IL",B1="MO",B1="AR",B1="LA",B1="MS",B1="AL",B1="GA",B1="FL",B1="DC",B1="DE",B1="NJ",B1="MA",B1="KY",B1="IN",B1="MI",B1="MD")),C1-4,IF(AND(C1-4<=TODAY(),NETWORKDAYS(C1-4,C1)=5,A1="WEST",OR(B1="TN",B1="ND",B1="SD",B1="NE",B1="KS",B1="MN",B1="WI",B1="OH",B1="IA",B1="IL",B1="MO",B1="AR",B1="LA",B1="MS",B1="AL",B1="GA",B1="FL",B1="DC",B1="DE",B1="NJ",B1="MA",B1="KY",B1="IN",B1="MI",B1="MD")),TODAY(),IF(AND(C1-6>TODAY(),NETWORKDAYS(C1-4,C1)=4,A1="WEST",OR(B1="TN",B1="ND",B1="SD",B1="NE",B1="KS",B1="MN",B1="WI",B1="OH",B1="IA",B1="IL",B1="MO",B1="AR",B1="LA",B1="MS",B1="AL",B1="GA",B1="FL",B1="DC",B1="DE",B1="NJ",B1="MA",B1="KY",B1="IN",B1="MI",B1="MD")),C1-6,IF(AND(C1-6<=TODAY(),NETWORKDAYS(C1-4,C1)=4,A1="WEST",OR(B1="TN",B1="ND",B1="SD",B1="NE",B1="KS",B1="MN",B1="WI",B1="OH",B1="IA",B1="IL",B1="MO",B1="AR",B1="LA",B1="MS",B1="AL",B1="GA",B1="FL",B1="DC",B1="DE",B1="NJ",B1="MA",B1="KY",B1="IN",B1="MI",B1="MD")),TODAY(),IF(AND(C1-6>TODAY(),NETWORKDAYS(C1-4,C1)=3,A1="WEST",OR(B1="TN",B1="ND",B1="SD",B1="NE",B1="KS",B1="MN",B1="WI",B1="OH",B1="IA",B1="IL",B1="MO",B1="AR",B1="LA",B1="MS",B1="AL",B1="GA",B1="FL",B1="DC",B1="DE",B1="NJ",B1="MA",B1="KY",B1="IN",B1="MI",B1="MD")),C1-6,IF(AND(C1-6<=TODAY(),NETWORKDAYS(C1-4,C1)=3,A1="WEST",OR(B1="TN",B1="ND",B1="SD",B1="NE",B1="KS",B1="MN",B1="WI",B1="OH",B1="IA",B1="IL",B1="MO",B1="AR",B1="LA",B1="MS",B1="AL",B1="GA",B1="FL",B1="DC",B1="DE",B1="NJ",B1="MA",B1="KY",B1="IN",B1="MI",B1="MD")),TODAY(),IF(AND(C1-3>TODAY(),NETWORKDAYS(C1-3,C1)=4,A1="WEST",OR(B1="OR",B1="WA",B1="ID",B1="MT",B1="WY",B1="CO",B1="NM",B1="OK",B1="TX",B1="AK",B1="HI")),C1-3,IF(AND(C1-3<=TODAY(),NETWORKDAYS(C1-3,C1)=4,A1="WEST",OR(B1="OR",B1="WA",B1="ID",B1="MT",B1="WY",B1="CO",B1="NM",B1="OK",B1="TX",B1="AK",B1="HI")),TODAY(),IF(AND(C1-5>TODAY(),NETWORKDAYS(C1-3,C1)=2,A1="WEST",OR(B1="OR",B1="WA",B1="ID",B1="MT",B1="WY",B1="CO",B1="NM",B1="OK",B1="TX",B1="AK",B1="HI")),C1-5,IF(AND(C1-5<=TODAY(),NETWORKDAYS(C1-3,C1)=2,A1="WEST",OR(B1="OR",B1="WA",B1="ID",B1="MT",B1="WY",B1="CO",B1="NM",B1="OK",B1="TX",B1="AK",B1="HI")),TODAY(),IF(AND(C1-5>TODAY(),NETWORKDAYS(C1-3,C1)=3,A1="WEST",OR(B1="OR",B1="WA",B1="ID",B1="MT",B1="WY",B1="CO",B1="NM",B1="OK",B1="TX",B1="AK",B1="HI")),C1-5,IF(AND(C1-5<=TODAY(),NETWORKDAYS(C1-3,C1)=3,A1="WEST",OR(B1="OR",B1="WA",B1="ID",B1="MT",B1="WY",B1="CO",B1="NM",B1="OK",B1="TX",B1="AK",B1="HI")),TODAY(),IF(AND(C1-2>TODAY(),NETWORKDAYS(C1-2,C1)=3,A1="WEST",OR(B1="UT",B1="NV",B1="AZ")),C1-2,IF(AND(C1-2<=TODAY(),NETWORKDAYS(C1-2,C1)=3,A1="WEST",OR(B1="UT",B1="NV",B1="AZ")),TODAY(),IF(AND(C1-4>TODAY(),NETWORKDAYS(C1-2,C1)=2,A1="WEST",OR(B1="UT",B1="NV",B1="AZ")),C1-4,IF(AND(C1-4<=TODAY(),NETWORKDAYS(C1-2,C1)=2,A1="WEST",OR(B1="UT",B1="NV",B1="AZ")),TODAY(),IF(AND(C1-4>TODAY(),NETWORKDAYS(C1-2,C1)=1,A1="WEST",OR(B1="UT",B1="NV",B1="AZ")),C1-4,IF(AND(C1-4<=TODAY(),NETWORKDAYS(C1-2,C1)=1,A1="WEST",OR(B1="UT",B1="NV",B1="AZ")),TODAY(),IF(AND(C1-1>TODAY(),NETWORKDAYS(C1-1,C1)=2,A1="WEST",OR(B1="CA")),C1-1,IF(AND(C1-1<=TODAY(),NETWORKDAYS(C1-1,C1)=2,A1="WEST",OR(B1="CA")),TODAY(),IF(AND(C1-1>TODAY(),NETWORKDAYS(C1-1,C1)=1,A1="WEST",OR(B1="CA")),C1-3,IF(AND(C1-1<=TODAY(),NETWORKDAYS(C1-1,C1)=1,A1="WEST",OR(B1="CA")),TODAY()))))))))))))))))))))))))))

(EAST ONLY)

=IF(AND(C1-7>TODAY(),NETWORKDAYS(C1-5,C1)=5,A1="EAST",OR(B1="CA",B1="NV",B1="WY",B1="OR",B1="WA",B1="MT",B1="UT",B1="ID",B1="HI")),C1-7,IF(AND(C1-7<=TODAY(),NETWORKDAYS(C1-5,C1)=5,A1="EAST",OR(B1="CA",B1="NV",B1="WY",B1="OR",B1="WA",B1="MT",B1="UT",B1="ID",B1="HI")),TODAY(),IF(AND(C1-7>TODAY(),NETWORKDAYS(C1-5,C1)=4,A1="EAST",OR(B1="CA",B1="NV",B1="WY",B1="OR",B1="WA",B1="MT",B1="UT",B1="ID",B1="HI")),C1-7,IF(AND(C1-7<=TODAY(),NETWORKDAYS(C1-5,C1)=4,A1="EAST",OR(B1="CA",B1="NV",B1="WY",B1="OR",B1="WA",B1="MT",B1="UT",B1="ID",B1="HI")),TODAY(),IF(AND(C1-4>TODAY(),NETWORKDAYS(C1-4,C1)=5,A1="EAST",OR(B1="ND",B1="SD",B1="NE",B1="CO",B1="AZ",B1="NM")),C1-4,IF(AND(C1-4<=TODAY(),NETWORKDAYS(C1-4,C1)=5,A1="EAST",OR(B1="ND",B1="SD",B1="NE",B1="CO",B1="AZ",B1="NM")),TODAY(),IF(AND(C1-6>TODAY(),NETWORKDAYS(C1-4,C1)=4,A1="EAST",OR(B1="ND",B1="SD",B1="NE",B1="CO",B1="AZ",B1="NM")),C1-6,IF(AND(C1-6<=TODAY(),NETWORKDAYS(C1-4,C1)=4,A1="EAST",OR(B1="ND",B1="SD",B1="NE",B1="CO",B1="AZ",B1="NM")),TODAY(),IF(AND(C1-6>TODAY(),NETWORKDAYS(C1-4,C1)=3,A1="EAST",OR(B1="ND",B1="SD",B1="NE",B1="CO",B1="AZ",B1="NM")),C1-6,IF(AND(C1-6<=TODAY(),NETWORKDAYS(C1-4,C1)=3,A1="EAST",OR(B1="ND",B1="SD",B1="NE",B1="CO",B1="AZ",B1="NM")),TODAY(),IF(AND(C1-3>TODAY(),NETWORKDAYS(C1-3,C1)=4,A1="EAST",OR(B1="KS",B1="IA",B1="MN",B1="WI",B1="OK",B1="AR",B1="TX",B1="MA",B1="RI",B1="ME",B1="NH",B1="VT",B1="AK")),C1-3,IF(AND(C1-3<=TODAY(),NETWORKDAYS(C1-3,C1)=4,A1="EAST",OR(B1="KS",B1="IA",B1="MN",B1="WI",B1="OK",B1="AR",B1="TX",B1="MA",B1="RI",B1="ME",B1="NH",B1="VT",B1="AK")),TODAY(),IF(AND(C1-5>TODAY(),NETWORKDAYS(C1-3,C1)=2,A1="EAST",OR(B1="KS",B1="IA",B1="MN",B1="WI",B1="OK",B1="AR",B1="TX",B1="MA",B1="RI",B1="ME",B1="NH",B1="VT",B1="AK")),C1-5,IF(AND(C1-5<=TODAY(),NETWORKDAYS(C1-3,C1)=2,A1="EAST",OR(B1="KS",B1="IA",B1="MN",B1="WI",B1="OK",B1="AR",B1="TX",B1="MA",B1="RI",B1="ME",B1="NH",B1="VT",B1="AK")),TODAY(),IF(AND(C1-5>TODAY(),NETWORKDAYS(C1-3,C1)=3,A1="EAST",OR(B1="KS",B1="IA",B1="MN",B1="WI",B1="OK",B1="AR",B1="TX",B1="MA",B1="RI",B1="ME",B1="NH",B1="VT",B1="AK")),C1-5,IF(AND(C1-5<=TODAY(),NETWORKDAYS(C1-3,C1)=3,A1="EAST",OR(B1="KS",B1="IA",B1="MN",B1="WI",B1="OK",B1="AR",B1="TX",B1="MA",B1="RI",B1="ME",B1="NH",B1="VT",B1="AK")),TODAY(),IF(AND(C1-2>TODAY(),NETWORKDAYS(C1-2,C1)=3,A1="EAST",OR(B1="GA",B1="FL",B1="AL",B1="MS",B1="MO",B1="IN",B1="IL",B1="MI",B1="PA",B1="NY",B1="DC",B1="NJ",B1="CT",B1="MD",B1="WV",B1="KY",B1="OH",B1="LA",B1="TN",B1="DE")),C1-2,IF(AND(C1-2<=TODAY(),NETWORKDAYS(C1-2,C1)=3,A1="EAST",OR(B1="GA",B1="FL",B1="AL",B1="MS",B1="MO",B1="IN",B1="IL",B1="MI",B1="PA",B1="NY",B1="DC",B1="NJ",B1="CT",B1="MD",B1="WV",B1="KY",B1="OH",B1="LA",B1="TN",B1="DE")),TODAY(),IF(AND(C1-4>TODAY(),NETWORKDAYS(C1-2,C1)=2,A1="EAST",OR(B1="GA",B1="FL",B1="AL",B1="MS",B1="MO",B1="IN",B1="IL",B1="MI",B1="PA",B1="NY",B1="DC",B1="NJ",B1="CT",B1="MD",B1="WV",B1="KY",B1="OH",B1="LA",B1="TN",B1="DE")),C1-4,IF(AND(C1-4<=TODAY(),NETWORKDAYS(C1-2,C1)=2,A1="EAST",OR(B1="GA",B1="FL",B1="AL",B1="MS",B1="MO",B1="IN",B1="IL",B1="MI",B1="PA",B1="NY",B1="DC",B1="NJ",B1="CT",B1="MD",B1="WV",B1="KY",B1="OH",B1="LA",B1="TN",B1="DE")),TODAY(),IF(AND(C1-4>TODAY(),NETWORKDAYS(C1-2,C1)=1,A1="EAST",OR(B1="GA",B1="FL",B1="AL",B1="MS",B1="MO",B1="IN",B1="IL",B1="MI",B1="PA",B1="NY",B1="DC",B1="NJ",B1="CT",B1="MD",B1="WV",B1="KY",B1="OH",B1="LA",B1="TN",B1="DE")),C1-4,IF(AND(C1-4<=TODAY(),NETWORKDAYS(C1-2,C1)=1,A1="EAST",OR(B1="GA",B1="FL",B1="AL",B1="MS",B1="MO",B1="IN",B1="IL",B1="MI",B1="PA",B1="NY",B1="DC",B1="NJ",B1="CT",B1="MD",B1="WV",B1="KY",B1="OH",B1="LA",B1="TN",B1="DE")),TODAY(),IF(AND(C1-1>TODAY(),NETWORKDAYS(C1-1,C1)=2,A1="EAST",OR(B1="VA",B1="NC",B1="SC")),C1-1,IF(AND(C1-1<=TODAY(),NETWORKDAYS(C1-1,C1)=2,A1="EAST",OR(B1="VA",B1="NC",B1="SC")),TODAY(),IF(AND(C1-3>TODAY(),NETWORKDAYS(C1-1,C1)=1,A1="EAST",OR(B1="VA",B1="NC",B1="SC")),C1-3,IF(AND(C1-3<=TODAY(),NETWORKDAYS(C1-1,C1)=1,A1="EAST",OR(B1="VA",B1="NC",B1="SC")),TODAY()))))))))))))))))))))))))))