Excel guide · 4 min read
How to calculate OTIF (on time in full) in Excel
=MAXIFS(delivered,order,id)<=promised, in full is =COUNTIFS(order,id,short,TRUE)=0, and OTIF is =AND(on_time,in_full). OTIF % is the TRUE count over the number of orders: 2 of 6 orders in the sample, 33%.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Customer | Promised | Delivered | Ordered | Shipped | Short | Order | Customer | Promised | On time | |
| 2 | SO-101 | Acme Foods | Oct 5, 2026 | Oct 5, 2026 | 120 | 120 | FALSE | SO-101 | Acme Foods | 46300 | TRUE | |
| 3 | SO-101 | Acme Foods | Oct 5, 2026 | Oct 5, 2026 | 60 | 60 | FALSE | SO-102 | Brightmart | 46303 | FALSE | |
| 4 | SO-102 | Brightmart | Oct 8, 2026 | Oct 9, 2026 | 200 | 200 | FALSE | SO-103 | Acme Foods | 46307 | TRUE | |
| 5 | SO-103 | Acme Foods | Oct 12, 2026 | Oct 12, 2026 | 80 | 72 | TRUE | SO-104 | Corley & Sons | 46310 | TRUE | |
| 6 | SO-103 | Acme Foods | Oct 12, 2026 | Oct 12, 2026 | 40 | 40 | FALSE | SO-105 | Brightmart | 46329 | FALSE | |
| 7 | SO-104 | Corley & Sons | Oct 15, 2026 | Oct 14, 2026 | 150 | 150 | FALSE | SO-106 | Corley & Sons | 46335 | FALSE | |
| 8 | SO-104 | Corley & Sons | Oct 15, 2026 | Oct 15, 2026 | 30 | 30 | FALSE | |||||
| 9 | SO-105 | Brightmart | Nov 3, 2026 | Nov 6, 2026 | 90 | 84 | TRUE | |||||
| 10 | SO-106 | Corley & Sons | Nov 9, 2026 | Nov 9, 2026 | 75 | 75 | FALSE | |||||
| 11 | SO-106 | Corley & Sons | Nov 9, 2026 | Nov 10, 2026 | 50 | 50 | FALSE |
Download this sheet as an Excel practice file (.xlsx)
Start from order lines, then build one row per order
Most systems export deliveries one line per product: order number in A, customer in B, promised date in C, delivered date in D, ordered quantity in E and shipped quantity in F. In G2, =F2<E2 flags a short line, filled down.
OTIF is measured per order, so list each order once in I2:I7. In Excel 365, =UNIQUE(A2:A11) builds that list for you; in older versions, copy the column and use Data > Remove Duplicates. J2 fetches the customer with =XLOOKUP(I2,$A$2:$A$11,$B$2:$B$11) and K2 the promised date with =MINIFS($C$2:$C$11,$A$2:$A$11,I2), the earliest promise on the order.
On time: the last line must arrive by the promise
An order isn't delivered until its last line is. In L2, =MAXIFS($D$2:$D$11,$A$2:$A$11,I2)<=K2+$Q$1 takes the latest delivery date for the order and compares it with the promise plus the grace days in Q1 (0 here). Early counts as on time; if your customer penalises early deliveries too, test a window instead, as in the FAQ.
SO-106 shows why the latest date matters: one line arrived on 9 Nov as promised, the other a day later, so the whole order is late. A check on the first line alone would have scored it on time.
Result: 0.5 shows as 50% with Percent format
0.5, 50% on time: SO-101, SO-103 and SO-104 arrived by their promised date.
What does a one-day grace window change?
Type 1 in Q1 and SO-102 and SO-106, both one day late, now count as on time. The formula below shows the on-time rate with the grace day applied directly.
Result: 0.83333333333
0.8333…, 83% on time with a one-day window instead of 50%. Agree the window with the customer before you report it.
In full: no short lines on the order
In M2, =COUNTIFS($A$2:$A$11,I2,$G$2:$G$11,TRUE)=0 counts the order's short lines and returns TRUE when there are none. SO-103 shipped 72 of 80 on one line, so it fails even though the other line was complete. That's the strict definition most retailers use: one short line fails the order. The share of units shipped is a different measure, the order fill rate. Units that miss the first shipment and follow later are measured by the backorder rate.
Result: 0.66666666667 shows as 67% with Percent format
0.6667, 67% in full: 4 of the 6 orders had no short lines (SO-103 and SO-105 were short).
How does that compare with the unit fill rate?
Units shipped over units ordered looks far better, because two short lines barely move the total.
Result: 0.9843575419
0.9844, 98.4% of units shipped, while only 67% of orders were complete.
OTIF % overall, by customer and by month
In N2, =AND(L2,M2) is TRUE only when both are. Count the TRUEs and divide by the number of orders.
OTIF can never be higher than the lower of the two rates, because an order has to pass both tests. Here on time is 50% and in full is 67%, so OTIF is at most 50%; it comes out at 33% because the late orders and the short orders are mostly different ones. If your OTIF is above either rate, a formula is counting lines in one place and orders in another.
Result: 0.33333333333 shows as 33% with Percent format
0.3333, 33% OTIF: only SO-101 and SO-104 were both on time and in full.
What is OTIF for one customer?
COUNTIFS the customer's OTIF orders and divide by COUNTIF of all their orders.
Result: 0.5
0.5: Corley & Sons had 2 orders and SO-104 passed while SO-106 was a day late.
And for one month?
Filter on the promised date between the first and last day of the month. October covers SO-101 to SO-104.
Result: 0.5
0.5: 2 of the 4 October orders were OTIF. November's two orders both failed.
Common mistakes
Scoring OTIF per line instead of per order
Build one row per order and use MAXIFS for the last delivery and COUNTIFS for short lines.
Using the first line's delivery date
An order is delivered when its last line arrives, so take MAXIFS of the delivered dates.
Reporting unit fill rate as "in full"
In full is a yes/no per order. 98% of units can still mean a third of orders were incomplete.
Hard-coding a grace period
Keep the grace days in an input cell and state it next to the result, so everyone knows which definition you used.
FAQ
What is the OTIF formula?
OTIF % = orders delivered both on time and in full / total orders. In Excel, flag each order with =AND(on_time,in_full) and divide COUNTIF of TRUE by the number of orders.
Is OTIF measured per order or per line?
Usually per order, but some customers define it per line or per delivery. Check the definition in the supply agreement and build the table at that level.
How do I count early deliveries as a fail?
Test a window instead of a deadline: on time when the delivered date is on or after promised minus the early days and on or before promised plus the grace days, for example =AND(d>=p-1,d<=p+1).
Why is OTIF lower than both on-time and in-full rates?
An order must pass both. When the late orders and the short orders are different ones, OTIF falls below both rates, but it can never be above the lower one.
Functions used
MAXIFSLargest value that matchesMINIFSSmallest value that matchesCOUNTIFSCounts with several conditionsCOUNTIFCounts cells that matchXLOOKUPModern lookup in any directionUNIQUERemoves duplicates, spillsANDTrue if all tests are trueROWSNumber of rows in a rangeSUMPRODUCTMultiply then addSUMAdds numbersDATEBuilds a date
Related guides
- How to calculate supplier lead time in Excel
- How to calculate order fill rate in Excel
- How to calculate backorder rate in Excel
- How to calculate SLA compliance in Excel
- MAX IF in Excel: find the largest value that meets a condition
- How to calculate stockout rate in Excel
- How to build a source of hire report in Excel
Updated