Software

Use Excel's Workday function to determine delivery dates

Your customers will appreciate knowing exactly when they can expect their orders to arrive -- and Excel makes it easy to set up a calculation that can tell them.

Instead of telling customers they should expect to receive their orders within 10 business days, you can give them an expected date of arrival. Follow these steps to set up a worksheet that lets Excel calculate the delivery date:

  1. Enter the headings for Order No., Order Date, and Delivery Date, as shown in Figure A.

Figure A

  1. Select N2:O2.
  2. Press Shift + Ctrl + Down Arrow.
  3. Right-click the selection and choose Format Cells.
  4. Select Date under Category and 03/14/01 under Type (Figure B).
  5. Click OK.

Figure B

  1. Select J2.
  2. Press Shift + Ctrl + Down Arrow.
  3. Right-click the selection and select Format Cells.
  4. Select Date under Category and 14-Mar-01 under Type.
  5. Click OK.
  6. Complete the Holiday Table as shown.
  7. Select J2:J9.
  8. Click in the Name box and enter Holidays_2008.
  9. Click in O2 and enter the following formula:
=WORKDAY(N2,10,Holidays_2008)

  1. Copy the formula to O2:O3 to obtain the delivery dates for Orders 2 and 3.

Figure C


Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox