Cellwise Start free

Excel guide · 4 min read

How to calculate OTIF (on time in full) in Excel

Quick answerAn order is OTIF when it arrived on or before the promised date and every line was delivered in full. Per order, on time is =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.

RowABCDEFGHIJKL
1OrderCustomerPromisedDeliveredOrderedShippedShortOrderCustomerPromisedOn time
2SO-101Acme FoodsOct 5, 2026Oct 5, 2026120120FALSESO-101Acme Foods46300TRUE
3SO-101Acme FoodsOct 5, 2026Oct 5, 20266060FALSESO-102Brightmart46303FALSE
4SO-102BrightmartOct 8, 2026Oct 9, 2026200200FALSESO-103Acme Foods46307TRUE
5SO-103Acme FoodsOct 12, 2026Oct 12, 20268072TRUESO-104Corley & Sons46310TRUE
6SO-103Acme FoodsOct 12, 2026Oct 12, 20264040FALSESO-105Brightmart46329FALSE
7SO-104Corley & SonsOct 15, 2026Oct 14, 2026150150FALSESO-106Corley & Sons46335FALSE
8SO-104Corley & SonsOct 15, 2026Oct 15, 20263030FALSE
9SO-105BrightmartNov 3, 2026Nov 6, 20269084TRUE
10SO-106Corley & SonsNov 9, 2026Nov 9, 20267575FALSE
11SO-106Corley & SonsNov 9, 2026Nov 10, 20265050FALSE

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.

=COUNTIF(L2:L7,TRUE)/ROWS(L2:L7)

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.

=SUMPRODUCT(--(MAXIFS(D2:D11,A2:A11,I2:I7)<=K2:K7+1))/ROWS(I2:I7)

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.

=COUNTIF(M2:M7,TRUE)/ROWS(M2:M7)

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.

=SUM(F2:F11)/SUM(E2:E11)

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.

=COUNTIF(N2:N7,TRUE)/ROWS(N2:N7)

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.

=COUNTIFS(J2:J7,"Corley & Sons",N2:N7,TRUE)/COUNTIF(J2:J7,"Corley & Sons")

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.

=COUNTIFS(K2:K7,">="&DATE(2026,10,1),K2:K7,"<="&DATE(2026,10,31),N2:N7,TRUE)/COUNTIFS(K2:K7,">="&DATE(2026,10,1),K2:K7,"<="&DATE(2026,10,31))

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

Related guides

Updated