The Ultimate Guide: Handling Date & Time Differences in Excel for Accurate On-Time/Late Delivery Tracking

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical.

Are you struggling with tracking deliveries that span multiple days? Do your conditional formatting rules fail when the delivery time crosses midnight or extends beyond 24 hours?

This comprehensive guide will walk you through a step-by-step solution to accurately track on-time and late deliveries in Excel, even across different dates.

The Problem: Conditional Formatting Fails Across Different Dates

Many users face issues when their delivery times cross midnight or extend beyond 24 hours. Standard conditional formatting rules often fail because they don’t account for date differences properly:

• =IF(B2<D2,"true","false") [cells turn red]
• =IF(B2>D2,”true”,”false”) [cells turn green]

The Root Cause: Excel’s Date and Time Representation

In Excel, dates are stored as integers (e.g., July 14, 2025 is represented by the integer 45859), while times are fractions of a day. This dual representation can cause issues when comparing date-time values that span multiple days.

The Solution: Convert and Compare Correctly

To accurately track on-time vs late deliveries, you need to ensure both your scheduled delivery time (Column N) and actual delivery time (Column E) are correctly formatted as dates/times. Here’s how:

Step-by-Step Guide for Accurate On-Time/Late Tracking

1. Ensure Correct Date & Time Formatting in Column N:

• Format column N (scheduled delivery) as a custom date/time format: m/d/yyyy h:mm.
• Ensure the values are actual dates/times, not text or general numbers.

2. Convert Text Entry in Column E to Time:

• Format column E (actual delivery) as time: h:mm AM/PM.
• Use a helper column F to convert the text entry hhmm into an actual time value using this formula:

  =TIME(LEFT(E2, 2), RIGHT(E2, 2))

3. Create Helper Columns for Date Handling:

• In column G: Use the TODAY() function to get today’s date.

  =TODAY()

• In column H: Combine today’s date with the time from helper column F:

  =G2 + TIME(LEFT(E2, 2), RIGHT(E2, 2))

4. Update Conditional Formatting Rules:

• For on-time deliveries (green):
  =H2 <= N2 + TIME(3,0,0)
• For late deliveries (red):
  =H2 > N2 + TIME(3,0,0)

5. Automate Date Handling with VBA:

• To stop the TODAY() function from updating once a time is entered:

  Private Sub Worksheet_Change(ByVal Target As Range)
              If Not Intersect(Target, Me.Range("E:E")) Is Nothing Then
                  Application.EnableEvents = False
                  Target.Offset(0, 1).Value = TODAY()
                  Application.EnableEvents = True
              End If
          End Sub

• Add this VBA code to the worksheet module for column E.

6. Handle Deliveries Across Multiple Days:

• For deliveries spanning more than 24 hours, use a formula that accounts for date differences:

  =IF(AND(H2 > N2 + TIME(3,0,0), H2 - N2 <= 1/24), "On Time", "Late")

• This ensures deliveries made within the grace period (even if crossing midnight) are marked correctly.

Extra Tip: Automate Timestamp Entry with VBA

To automatically timestamp when a delivery time is entered:

• Use this VBA code in your worksheet module:

  Private Sub Worksheet_Change(ByVal Target As Range)
              If Not Intersect(Target, Me.Range("E:E")) Is Nothing Then
                  Application.EnableEvents = False
                  Target.Offset(0, 2).Value = NOW()
                  Application.EnableEvents = True
              End If
          End Sub

• This will automatically record the exact time of entry in column G.

Advanced Variation: Countdown Timer for Late Deliveries

• To create a countdown timer that continues past zero:

=IF(E2="", N2 + TIME(3, 0, 0) - NOW(), "Delivery Made")

• This formula will show the remaining time until delivery becomes late and continue counting after reaching zero.

Conclusion: Accurate On-Time/Late Delivery Tracking in Excel

The key to accurate on-time/late tracking is ensuring both scheduled and actual times are correctly formatted as date/time values. By using helper columns, conditional formatting rules, and VBA automation for timestamping, you can create a robust system that handles deliveries across multiple days.

For more advanced Excel features like auditing formulas or automating repetitive tasks, check out CelTools, which offers 70+ extra tools to enhance your productivity in Excel. CelTools can help you manage complex spreadsheets with ease.

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical.