Cellwise Start free

Excel guide · 4 min read

How to calculate supplier lead time in Excel

Quick answerLead time for a PO line is the received date minus the order date: =D2-C2 gives calendar days. For working days use =NETWORKDAYS(C2,D2,holidays)-1, because NETWORKDAYS counts both the start and the end day. Per supplier, use AVERAGEIFS and MAXIFS, and MEDIAN(IF(...)) when one stuck shipment distorts the average.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1POSupplierOrderedReceivedLead daysWorking daysQuotedStatusSupplierQuoted days
2P-501ApexAug 31, 2026Sep 14, 202614914On timeApex14
3P-502BrightwaySep 2, 2026Sep 25, 2026231621LateBrightway21
4P-503CormaSep 8, 2026Sep 17, 20269710On timeCorma10
5P-504ApexSep 15, 2026Oct 1, 2026161214Late
6P-505BrightwayAug 24, 2026Oct 21, 2026584121Late
7P-506CormaOct 5, 2026Oct 14, 20269710On time
8P-507ApexOct 12, 2026Oct 23, 202611914On time
9P-508BrightwayOct 19, 2026Nov 6, 2026181421On time
10P-509CormaOct 26, 202610Open - late
11P-510ApexNov 2, 202614Open

Download this sheet as an Excel practice file (.xlsx)

Lead time per PO line

List PO lines with the supplier in B, the order date in C and the received date in D (blank while the PO is open). Lead days in E2: =IF(D2="","",D2-C2). The IF keeps open lines blank instead of showing a huge negative number, and functions like AVERAGEIFS skip the blanks. If you raise POs in Excel, the free purchase order template already holds the PO date and deliver-by date for each order.

If the result shows as a date in January 1900, the formula is right and the format isn't: set the column to General or Number. Excel copied the date format from the cells you subtracted.

=D2-C2

Result: 14

14: P-501 to Apex was ordered on 31 August and received on 14 September, 14 calendar days.

Working days: NETWORKDAYS minus one

Suppliers often quote in working days. =NETWORKDAYS(C2,D2,$M$2:$M$3) counts weekdays between the two dates, skipping the holidays listed in M2:M3, but it includes both ends: an order placed Monday and received Tuesday counts as 2. Subtract 1 so same-day receipt is 0 and next-day is 1, matching how lead time is quoted.

P-501 spanned two weekends and Labor Day on 7 September, so 14 calendar days are 9 working days. Keep the holiday list in its own range and update it each year; without it, holidays count as working days and suppliers look slower than they were.

=NETWORKDAYS(C2,D2,M2:M3)-1

Result: 9

9: P-501's lead time in working days, after weekends and the 7 September holiday.

Average, worst and median lead time per supplier

Average per supplier: =AVERAGEIFS(E2:E11,B2:B11,"Apex",D2:D11,"<>"). The D2:D11,"<>" pair keeps only received lines, so an open PO never sneaks in. Apex averages about 13.7 days. Worst case: =MAXIFS(E2:E11,B2:B11,"Brightway",D2:D11,"<>") returns 58.

That 58 is P-505, a shipment that sat in customs. It drags Brightway's average to 33 days, although its other two deliveries took 23 and 18. The median (the middle value) isn't moved by one extreme, so it's the better "typical" lead time: =MEDIAN(IF(B2:B11="Brightway",IF(E2:E11<>"",E2:E11))). In Excel 2019 or earlier, confirm it with Ctrl+Shift+Enter; in Excel 365 just press Enter.

Report both: the median for planning, the worst case for safety stock. A supplier whose worst case keeps creeping up is a risk even if its median looks fine.

=MEDIAN(IF(B2:B11="Brightway",IF(E2:E11<>"",E2:E11)))

Result: 23

23: Brightway's median lead time, against an average of 33 that one 58-day shipment inflates.

On time against the quoted lead time

Keep each supplier's quoted lead time in a table (J:K) and pull it onto each line with =XLOOKUP(B2,$J$2:$J$4,$K$2:$K$4) in G2. A status column in H2 then covers every case: =IF(D2="",IF(C2+G2<$P$1,"Open - late","Open"),IF(E2<=G2,"On time","Late")).

Count on-time delivery per supplier with =COUNTIFS(B2:B11,"Apex",H2:H11,"On time")/COUNTIFS(B2:B11,"Apex",D2:D11,"<>"): 2 of Apex's 3 received lines, 66.7%. Brightway manages 1 of 3. Across all received lines it's 5 of 8, 62.5%. Make sure the quote and your measure use the same unit; if the supplier quotes working days, compare against column F instead of E.

=COUNTIFS(H2:H11,"On time")/COUNTIFS(D2:D11,"<>")

Result: 0.625 shows as 62.5% with Percent format

0.625: 5 of the 8 received PO lines arrived within the supplier's quoted lead time.

Open POs that are already late

Received lines only tell you about the past. The status formula also checks open lines: expected date = order date + quoted lead time, and if that's before the report date in P1 the line is "Open - late". Keep the report date in a cell, or use =TODAY() there for a live sheet.

List them for the morning chase-up in Excel 365 with =FILTER(A2:A11,H2:H11="Open - late"). P-509 to Corma was due on 5 November; =P1-(C10+G10) shows it's 6 days overdue. P-510 to Apex isn't due until 16 November, so it stays "Open".

=FILTER(A2:A11,H2:H11="Open - late")

Result: P-509

P-509: the only open PO past its expected date.

Common mistakes

Using NETWORKDAYS without subtracting 1

NETWORKDAYS counts both ends; subtract 1 so next-day delivery is 1 working day, not 2.

Letting open POs into the average

Leave lead days blank for open lines and add a received-date "<>" criteria pair to AVERAGEIFS and MAXIFS.

Planning with the average when there's an outlier

Use the median for the typical lead time and MAXIFS for the worst case.

Forgetting the holiday list

Pass a holidays range to NETWORKDAYS and keep it up to date.

FAQ

How do I calculate lead time between two dates in Excel?

Subtract the order date from the received date: =D2-C2 gives calendar days. Format the result as General if it shows as a date.

How do I calculate lead time in working days in Excel?

Use =NETWORKDAYS(order_date, received_date, holidays)-1. NETWORKDAYS skips weekends and listed holidays but counts both the first and last day.

How do I calculate average lead time per supplier?

Use AVERAGEIFS on the lead time column with the supplier as the criteria, plus a criteria pair that keeps only received lines: =AVERAGEIFS(E:E,B:B,"Apex",D:D,"<>").

Should I use average or median lead time?

Median for the typical lead time, because one delayed shipment can pull the average far up. Keep the worst case (MAXIFS) as well for safety stock.

Functions used

Related guides

Updated