How to Calculate Supplier Lead Time in Excel

Calculate lead time for each purchase order, the average lead time per supplier, and the worst case you need to plan safety stock around.

You will learn to

• Calculate lead time from order and receipt dates
• Average lead time for one supplier
• Find a supplier’s longest lead time

How it works

Lead time is the number of days between placing a purchase order and receiving the goods: received date − order date. Because Excel dates are numbers, a simple subtraction gives calendar days; use NETWORKDAYS if your suppliers quote business days.

The average lead time tells you when to reorder, but the longest lead time tells you how much safety stock to carry. For one supplier, =SUMPRODUCT((B2:B11=H1)*(D2:D11-C2:C11))/COUNTIF(B2:B11,H1) gives the average without a helper column, and =MAX((B2:B11=H1)*(D2:D11-C2:C11)) gives the worst case.

=D4-C4