Formula Errors

How to Fix Excel Formulas for On-Time and Late Departures

Maira Mehtab Sep 27, 2026 871 views

Question details

The user needs to fix unreliable departure-tracking formulas that are returning incorrect results or #REF! errors.

Product: Excel
Device & OS: not provided
Scenario: Calculating scheduled versus actual departure times to determine and count total, on-time, and late departures in a worksheet.
Observed behavior: The departure-tracking formulas fail to calculate properly due to invalid cell references (#REF! errors) and potential mismatch issues with time formats.

Before you start

Before modifying your formulas, document your worksheet layout and verify that all scheduled and actual time data is formatted as actual time values rather than plain text.

Solution 1Recommended

Resolve #REF! Errors and Verify Column Layout

Identify and repair broken cell references that cause the #REF! error in your departure formulas, ensuring data points to the correct columns.

A #REF! error occurs when a formula refers to a cell that is not valid, usually because columns or rows have been deleted or pasted over. Repairing these references is the first mandatory step before checking time formats.

Locate the broken formula

Select the cell displaying the #REF! error and look at the formula bar to identify the missing reference.

Update the cell references

Replace the #REF! portion of the formula with the correct cell range containing your scheduled or actual departure times.

Document column headers

Ensure each column is clearly labeled (e.g., 'Scheduled Time', 'Actual Departure') so you can accurately reference them when rewriting tracking calculations.

Important: If you frequently insert or delete columns, consider using structured Excel Tables or named ranges to prevent reference errors.

Solution 2

Convert Text Values to Actual Excel Time Formats

Formulas comparing times will fail if the times are stored as text. You must force Excel to recognize them as valid time values.

Solution 3

Calculate Departures with COUNTIF and COUNTIFS

Once references and formats are fixed, use conditional counting functions to track your on-time, late, and total departures accurately.

Efficient Formula Management

Track Departures Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides a robust, user-friendly environment for managing time calculations. Easily troubleshoot formula errors, format time data accurately, and apply advanced functions like COUNTIFS to track your on-time and late departures.

1. Open your worksheet: Launch WPS Spreadsheet and open your departure tracking document.
2. Format time data: Highlight the time columns and press Ctrl+1 to open the Format Cells dialog, ensuring 'Time' is selected.
3. Resolve errors: Use the built-in Error Checking feature under the Formulas tab to locate and resolve any #REF! errors in your sheet.
4. Enter tracking formulas: Input your =COUNTIFS() formulas to automatically calculate your total on-time and late departure metrics.

Fully compatible with Microsoft Excel formats and formula structures.Intuitive error-checking tools to quickly find and fix #REF! errors.Advanced cell formatting for accurate time and date calculations.Lightweight and free alternative for complex data tracking.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my time calculation return a #VALUE! error?

This usually happens when one or more cells in your formula contain text instead of actual time numbers. Check your cells, remove any hidden spaces, and format them properly as Time.

How do I handle departures that happen the next day?

If a departure crosses midnight, a standard subtraction or comparison might fail. You can handle this by adding 1 to the end time or using the MOD function (e.g., =MOD(Actual-Scheduled, 1)) to correctly calculate the time difference.

Can I use conditional formatting to highlight late departures?

Yes. You can select your actual departure times, go to Conditional Formatting in the Home tab, and set a 'Greater Than' rule to highlight cells that exceed the scheduled departure time in red.

What is the difference between COUNTIF and COUNTIFS?

COUNTIF counts cells based on a single condition, such as finding all late departures. COUNTIFS allows you to count cells based on multiple conditions, such as finding late departures that also belong to a specific carrier.

Excel & Data Problems