How to Calculate a Ship Date That Skips Holidays in Excel

Calculate holiday-season ship and delivery dates with WORKDAY and a holiday list, and work back to an order-by date.

You will learn to

• Add handling days while skipping weekends and holidays
• Combine handling and transit days
• Count back from a deadline past holidays

How it works

WORKDAY(start_date, days, holidays) adds working days while skipping Saturdays, Sundays and every date in the holiday range. Keep the holidays in a small list on the sheet and lock it with $ (for example $G$2:$G$5) so the formula copies down the order list correctly.

The same function answers the customer’s favourite question — “when do I need to order by?” — when you use a negative number of days. Counting back five working days from January 5 skips New Year’s Day and the weekend, landing on December 28.

=WORKDAY(B2,C2,$G$2:$G$5)