TECHNOLOGY EXCEL - Strategic Finance

[Pages:2]TECHNOLOGY

EXCEL By Bill Jelen

Calculating Delivery Dates in Excel

Let's say that you want to calculate a delivery date in Excel. You have columns that indicate the shipping date, the number of days in transit, and the shipping service. You need to calculate the expected delivery date based on the work days of the shipping carrier.

The roots of this solution start back in Excel 97 with the WORKDAY function. If you specified a start date, a number of elapsed days, and a range containing holiday dates, the WORKDAY function would return a valid Monday through Friday date, always ignoring holidays.

The WORKDAY function was available in Excel 2003 and earlier, but only if you had enabled the Analysis TookPak add-in. This made it a dangerous choice to use--if someone who didn't have the Analysis ToolPak installed opened your worksheet, the WORKDAY function would return a #NAME? error. Beginning with Excel 2007, WORKDAY became part of the core Excel function set. In Excel 2010, Microsoft expanded and enhanced the function, offering WORKDAY.INTL. The enhancement included an extra argument to specify

Figure 1

the days--or day--that should be considered the weekend.

In this particular problem, each carrier could offer a different set of weekend days. If you are shipping UPS, deliveries won't happen on Saturday or Sunday. This would work fine with the old WORKDAY function. If you are shipping with the U.S. Postal Service (USPS), deliveries could happen on a Saturday. If you are shipping FedEx Home (FDXH), deliveries won't happen on Sunday or Monday. In other words, without this enhancement, the shipping calculation could fail if you tried to use the WORKDAY function for USPS or FedEx Home.

Setting Up the Holiday Table

In a blank section of your workbook, enter a list of holiday dates, as shown in Figure 1. Although Figure 1 includes the holiday name, this information isn't necessary for the formula. To simplify the formula, give the range of holidays a name. In Figure 1, you would follow these steps: 1. Select A2:A11. 2. Click in the Name box to the left of

the formula bar. 3. Type a name, such as HolidayList, and

press Enter. Remember that names can't include spaces.

Understanding the Weekend Argument

Previously, the old WORKDAY argument would always assume Saturday and Sunday were the weekend days. Starting with Excel 2010, the WORKDAY.INTL function allows for 14 different sets of weekend dates. A value of 1 assumes the typical weekend of Saturday and Sunday. Values 2-7 assume various other two-day weekends. Values 11-17 assume various one-day weekends. Table 1 shows the 14 possible values for the weekend argument.

60

STRATEGIC FINANCE I March 2012

Figure 2

Setting Up the Weekend Table

In this situation, the weekend argument varies by carrier. Figure 2 illustrates a named range called DaysOffTable. It converts each shipper code to one of the weekend schemes for the WORKDAY.INTL function.

Table 1. Values for Weekend Argument

Value 1 2 3 4 5 6 7 11 12 13 14 15 16 17

Weekend Days Saturday, Sunday Sunday, Monday Monday, Tuesday Tuesday, Wednesday Wednesday, Thursday Thursday, Friday Friday, Saturday Sunday only Monday only Tuesday only Wednesday only Thursday only Friday only Saturday only

USPS has a value of 11, which corresponds to Sunday-only as the weekend. FDXH uses a value of 2, which means a weekend of Sunday and Monday.

Putting It All Together

The final formula is shown

in Figure 3. The formula for

cell E2 is =WORKDAY.

INTL(B2,D2,VLOOKUP(C2,DaysOffTable,2,

FALSE),HolidayList). This formula starts

out simply enough, specifying the ship

date from cell B2 and the days in transit

from cell D2. To derive the Weekend

value, a VLOOKUP function uses the

Figure 3

shipper code in cell

C2 to find the cor-

rect code from the

table in Figure 2.

The fourth argu-

ment points to the

range of holiday

dates from Figure 1.

product, however, both WORKDAY and WORKDAY.INTL will never automatically format the result as a date. The result will instead be a number, such as 41052. This is the correct answer, but Excel is using a General format instead of a Date format. (May 23, 2012, is 41,052 days after December 31, 1899.) Once you've

Formatting the Answer

Often, Excel is able to figure out that you are looking for a date as your answer and will automatically format the cell to show a date. For example, if you had entered =B2+D2 in cell E2, Excel would have seen the formatting in cell B2 and automatically changed the format of cell E2 to a Date format as soon as you pressed Enter to finish the formula. Because WORKDAY wasn't originally part of the core Excel

entered your WORKDAY.INTL formula, format the cell as a Short Date using the dropdown in the Home tab of the ribbon. The answer will change back to the correct date. SF

Bill Jelen is the author of Learn Excel 2007-2010 From MrExcel and 33 other books. Send questions for future articles to IMA@.

March 2012 I STRATEGIC FINANCE 61

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download