Excel 2007 VBA If-THEN-ELSE Shipping Logistics

I have a spreadsheet with 3 columns: order type, order date, and target ship date. There are 4 order types: USA, USAPriority, Canada, and Med.

Each order type only ships out on a certain date.

USA order types can ship out 2 days after the order date,
USAPriority ship next day after order date.
Canada orders ship out the thursday of each week, if received by 11:59AM Wednesday,
Med orders ship out the Wednesday of each week if received by 11:59AM Tuesday.

I want to write a code that produces the target ship date based upon order type and order date.

Solution

You could nest all of the following into a single column but I'll do this step by step assuming new columns for each step. I'll assume order type is column A, order date is column B, and target ship date is column C.

The first thing you'll want to do is make an effective order date which strips the time out of the order date and adds a day if it is later than the ship time and we'll put this in column D. That would look like this =IF(TIME(HOUR(B2),MINUTE(B2),SECOND(B2))>TIME(11,59,0),DATE(YEAR(B2),MONTH(B2),DAY(B2))+1,DATE(YEAR(B2),MONTH(B2),DAY(B2)))

You can then do the target with this formula =IF(A2="USA",B2+2,IF(A2="Med",IF(WEEKDAY(D2)>4,D2+7-WEEKDAY(D2)+4,D2+4-WEEKDAY(D2)),IF(A2="Canada",IF(WEEKDAY(D2)>5,D2+7-WEEKDAY(D2)+5,D2+5-WEEKDAY(D2)),"Invalid")))

EDIT: Elaboration on how column D works:

the TIME function takes three arguments, Hour, Minute, and Second. What we're doing is getting the time out of the date+time value of B2 using TIME(HOUR(B2),MINUTE(B2),SECOND(B2)) and seeing if it is greater (later) than 11:59am TIME(11,59,0). When B2 is later than 11:59 we take the time off of the date+time with DATE(YEAR(B2),MONTH(B2),DAY(B2)) and add 1 day +1 and return that. If the time is earlier than 11:59 then we just return the current date.

You can change the cutoff time by changing the TIME(11,59,0) part of the IF statement to whatever time you want. If the cutoff is different for each different value that order type can be then wrap TIME(11,59,0) in an IF statement (really it'll be 2-3 if statements since there are three possibilities. As an alternative, if you have a lot of order types or your cutoff dates might change in the future you could make a lookup table and then use the VLOOKUP statement instead of having the time hard coded in the formula itself.