You exported your Shopify orders. You pulled your 3PL’s shipment data. You wrote a formula: =shipped_date - order_date. Simple.

Except the numbers don’t make sense. Some orders show negative fulfillment time. Others look way too slow when you know they shipped same-day. Your 3PL says they’re hitting 98% on-time. Your spreadsheet says 82%.

Someone’s wrong. And you can’t figure out who.

Calculating fulfillment time sounds simple until you try to define it. Timezones, business days, cutoffs, and what “shipped” actually means all quietly break your spreadsheet logic. This is why most DIY SLA tracking falls apart, and why your numbers and your 3PL’s can both be “correct.”

The Formula That Looks Simple

Here’s what most people start with:

Fulfillment Time = Shipped Date - Order Date

In Google Sheets or Excel, that’s just =B2-A2 where A is order created and B is shipped. Multiply by 24 to get hours. Done.

Until it isn’t.

Problem 1: Which Timezone Are We In?

Your Shopify order timestamps are in UTC. Always. Even if your store is set to Eastern time, the API returns UTC.

Your 3PL operates in… well, where are they? ShipBob has facilities in multiple timezones. Your regional 3PL might be Central. Amazon MCF facilities are everywhere.

When your 3PL marks something “shipped at 5:00 PM,” is that 5 PM their time? UTC? Your store’s timezone?

Here’s a real scenario:

Order placed: 2026-01-13 23:30:00 UTC (6:30 PM Eastern)
3PL marks shipped: 2026-01-14 14:00:00 (but they meant 2 PM Eastern, which is 19:00:00 UTC)

If your spreadsheet treats both as the same timezone, you get 14.5 hours. The actual fulfillment time was 19.5 hours. You’re off by 5 hours on a single order. Across hundreds of orders with different timezone assumptions, your aggregate metrics are meaningless.

The fix requires: Converting every timestamp to a common timezone before comparison. That means knowing what timezone each data source uses, which often isn’t documented.

Problem 2: Business Days vs Calendar Days

Your SLA says “2 business days.” Your formula calculates calendar days.

Google Sheets has NETWORKDAYS() which excludes weekends. Great. But it doesn’t know about:

Federal holidays: Is MLK day a business day for your 3PL? What about Columbus Day?
3PL-specific closures: Many 3PLs shut down the week between Christmas and New Year’s
Regional holidays: If you have 3PLs in multiple states or countries, holidays differ
Seasonal schedule changes: Some 3PLs run Saturday operations during peak season only

You could build a holiday calendar in your spreadsheet. A separate tab listing every non-business day. Then reference it in a custom formula. Then maintain it. Forever. For each 3PL.

=NETWORKDAYS(A2, B2, Holidays!$A$2:$A$50)

Except you forgot that your West Coast 3PL observes Cesar Chavez Day and your East Coast one doesn’t. Now your comparison is apples to oranges.

Problem 3: When Does the Clock Start?

Order placed Friday at 11:47 PM. When does the SLA clock start?

Options:

Immediately (11:47 PM Friday)
Next business day open (Monday 9 AM)
Next calendar day (Saturday 12:00 AM)
When your 3PL’s system received it (depends on integration lag)

Different 3PLs define this differently. Some start the clock when they acknowledge receipt in their WMS. Others use order creation time. Your SLA contract probably specifies this, but good luck encoding that logic in a spreadsheet formula.

And what about that 2 PM cutoff your 3PL has for same-day shipping?

Order at 1:30 PM: eligible for same-day
Order at 2:30 PM: next-day at best

Your spreadsheet formula is the same for both orders. It doesn’t know about cutoffs. So when you calculate “time to ship,” you’re comparing orders with fundamentally different expectations.

Problem 4: Date vs DateTime

Your Shopify export has timestamps: 2026-01-14T14:32:17-05:00

Your 3PL’s report has dates: 1/14/2026

That date could mean 12:00:00 AM or 11:59:59 PM. Twenty-four hours of ambiguity.

If you’re calculating in hours, this matters. If an order was placed at 3 PM and shipped “on 1/14,” was that 9 hours (shipped at midnight) or 33 hours (shipped at end of day)? Your spreadsheet has no way to know.

Some 3PLs export times in their reports. Some don’t. Some export times but round to the nearest hour. You end up with a patchwork of precision levels that you’re treating as equivalent data.

Problem 5: What Does “Shipped” Mean?

Your 3PL says shipped. But shipped when?

Label created: The shipping label was generated. Package might still be sitting on a shelf.
Packed: Order was picked and packed, waiting for carrier pickup.
Carrier pickup: Carrier physically scanned the package at the facility.
First carrier scan: Package hit the carrier’s network somewhere in transit.

Many 3PLs mark orders “shipped” when they create the label. That might be 3 PM. The carrier doesn’t pick up until 7 PM. Or the next morning. Your SLA calculation is measuring something different than actual handoff time.

Here’s what that looks like:

Event	Time	What Your Spreadsheet Sees
Order placed	10:00 AM	Order created
Label created	2:00 PM	”Shipped”
Carrier pickup	6:30 PM	(not captured)
First scan	9:00 PM	(not captured)

Your spreadsheet says 4 hours. Actual time to carrier handoff was 8.5 hours. If you’re measuring against a “same business day” SLA, the 4-hour number looks great. The 8.5-hour reality missed the cutoff.

Problem 6: Daylight Saving Time

Twice a year, an hour appears or disappears.

Orders placed during the transition can produce nonsense:

“Shipped in -1 hours” (when clocks fall back)
Missing hours in aggregate calculations
Orders that look like outliers but are just timezone artifacts

Most people don’t notice until someone asks “why does March always look weird?”

The Formula Is Now Unreadable

If you’ve tried to handle all of this, your “simple” formula now looks something like:

=IF(
  WEEKDAY(A2,2)>5,
  NETWORKDAYS(A2+1,B2,Holidays!$A:$A)-1,
  IF(
    HOUR(A2-TIME_OFFSET)>=14,
    NETWORKDAYS(A2+1,B2,Holidays!$A:$A),
    NETWORKDAYS(A2,B2,Holidays!$A:$A)-1
  )
) * 24 + MOD(B2-A2,1)*24

And this still doesn’t handle timezones correctly. Or different cutoffs by 3PL. Or the date-vs-datetime problem.

Every edge case is another nested IF. Every 3PL is another column of adjustments. The person who built it is the only one who understands it. When they leave, the spreadsheet becomes a black box that everyone’s afraid to touch.

The Conversation That Goes Nowhere

So you’ve built your spreadsheet. You’ve made decisions on all the edge cases. You present your findings to your 3PL:

“You missed SLA on 15% of orders last month.”

They pull up their dashboard:

“Our system shows 97% on-time.”

Now what?

You can’t explain exactly how you calculated it because the formula is a mess of nested conditionals and your holiday calendar might be wrong. They can’t explain exactly how they calculated it because it’s buried in their WMS logic. Neither side trusts the other’s numbers.

The conversation becomes:

“Your timezone handling is wrong.”
“No, yours is.”
“You’re not accounting for our cutoff time.”
“You’re measuring from label creation, not carrier pickup.”

Neither of you can prove anything. The argument goes nowhere. Trust erodes.

Why Most Companies Give Up

Here’s the uncomfortable truth: most companies don’t track 3PL SLAs at all.

Not because they don’t care. Because the tooling gap makes rigorous tracking so painful that it’s not worth the effort. You’d need to:

Build a system that ingests data from multiple sources
Normalize all timestamps to a common timezone
Maintain holiday calendars for each 3PL
Handle cutoff time logic per facility
Track actual carrier handoff, not just label creation
Calculate metrics consistently so you can defend your methodology

That’s not a spreadsheet. That’s a software project. And most ops teams have actual operations to run.

So they give up. They fly blind on fulfillment performance. They react to customer complaints instead of catching problems early. They have no leverage in 3PL negotiations because they can’t back up claims with data.

This isn’t just a merchant problem. It’s an industry problem.

Good 3PLs actually want to prove their performance. But they can’t trust merchant-provided data (because of all the above), and building custom dashboards for every client doesn’t scale.

Bad 3PLs benefit from the confusion. If nobody can accurately measure performance, nobody gets caught.

The result is an industry with surprisingly low accountability. Not because people don’t want accountability, but because the measurement problem is genuinely hard and nobody wants to solve it with spreadsheets anymore.

What a Real Solution Requires

To actually measure 3PL SLAs accurately, you need:

Webhook listeners capturing timestamps as events happen, not from periodic exports
Timezone handling that knows where each data point originates
Holiday calendars maintained per 3PL, updated annually
Cutoff time logic that varies by facility, service level, and season
Carrier integration to get actual pickup scans, not just label creation
A data model that distinguishes between date and datetime precision
Documentation so everyone agrees on what’s being measured

That’s the scope of the problem. It’s not a formula. It’s a system.

We built 3PL Pulse because we got tired of this exact problem. Timezone normalization, holiday handling, carrier pickup tracking, defensible methodology. The stuff that makes spreadsheet calculations a nightmare is just… how the software works.

But even if you never use us, at least now you know why your spreadsheet numbers don’t match your 3PL’s. It’s probably not user error. The problem is actually that hard.

Why Calculating Fulfillment Time Is Harder Than It Looks