Excel 2007 VBA If-THEN-ELSE Shipping Logistics

Question

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.

How much experience do you have with Excel formulas? You won't need VBA for this. — Jack, Jun 13 '13 at 15:23
Wouldn't it be easier to do this with pure excel spreadsheet functions, and then getting the results into VBA with Range()? — Simon, Jun 13 '13 at 15:27
I would say I have average excel experience. If you have a solution without using VBA I would appreciate it as well. — Newbie1331, Jun 13 '13 at 16:15

score 2 · Answer 1 · answered Jun 13 '13 at 16:46

2

Here's a simple solution without VBA. Assuming your three columns are A, B, and C, put this formula at the top of column C, then double click the bottom right corner to drag it down.

=IF(A2="USA",B2+2,IF(A2="Canada",(B2+(7-WEEKDAY(B2,2)+4)),IF(A2="Med",(B2+(7-WEEKDAY(B2,2)+3)),"Invalid order type")))

answered Jun 13 '13 at 16:46

Jack

2,750
2
27
36

This doesn't handle same day orders correctly such as in the OP's example. – Dean MacGregor Jun 13 '13 at 17:33

Dean MacGregor · Accepted Answer · 2013-06-14T20:52:05.400

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.

This could end up putting your USA order outs 3 days. You could correct this in the D column by having it inside an `IF(b2<>"USA"` clause — Dean MacGregor, Jun 13 '13 at 17:54
This works well but there is more to the problem than I first thought. The cutoff dates for shipping are different than the shipping dates. I will explain in edited question. — Newbie1331, Jun 13 '13 at 20:52
@Newbie1331 It should be relatively easy to accommodate cutoff times in the D column with additional IF statements. — Dean MacGregor, Jun 14 '13 at 17:14
Could you show me an example please, I am having trouble understanding the logic of the formulas. I have never used formulas to alter dates in Excel. — Newbie1331, Jun 14 '13 at 17:33

score 0 · Answer 3 · answered Jun 13 '13 at 18:05

VBA Solution

Insert this code into a module and use the function targetShipDate(orderType, orderDate) to get the target ship date.

Function targetShipDate(orderType As String, orderDate As Date)
    Select Case orderType
    Case "USA"
        targetShipDate = nextUsaShipDate(orderDate)
    Case "Canada"
        targetShipDate = nextCanadaShipDate(orderDate)
    Case "Med"
        targetShipDate = nextMedShipDate(orderDate)
    Case Else
        targetShipDate = Null
    End Select
End Function

Function nextCanadaShipDate(d As Date)
    nextCanadaShipDate = d - Weekday(d) + 5 + IIf(Weekday(d) >= 5, 7, 0)
    If Weekday(d) = 5 And Hour(d) <= 11 Then
        nextCanadaShipDate = nextCanadaShipDate - 7
    End If
End Function

Function nextMedShipDate(d As Date)
    nextMedShipDate = d - Weekday(d) + 4 + IIf(Weekday(d) >= 4, 7, 0)
    If Weekday(d) = 4 And Hour(d) <= 11 Then
        nextMedShipDate = nextMedShipDate - 7
    End If
End Function

Function nextUsaShipDate(d As Date)
    nextUsaShipDate = d + 2
End Function

I wrote this when I initially saw Jack's answer not handling same day cases. Dean MacGregor's answer is most likely what you are looking for if you prefer a formula. Added this incase anyone prefers a VBA solution for clarity within the worksheet. — ashareef, Jun 13 '13 at 18:07

Excel 2007 VBA If-THEN-ELSE Shipping Logistics

3 Answers3