Formula Errors

How to Fix the #VALUE! Error When Calculating Time Differences in Excel

Ayan Masood Oct 8, 2026 868 views

Question details

The user needs to resolve a #VALUE! error that appears when attempting to subtract time values.

How to Fix the #VALUE! Error When Calculating Time Differences in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating the difference between two lap times or precise durations entered with multiple decimal points (e.g., 1.28.848).
Observed behavior: Excel interprets times with two decimal points as text rather than valid numbers, throwing a #VALUE! error during subtraction. Additionally, negative time results yield further errors or a string of hash symbols (######).

Before you start

Verify that your time entries are using valid time delimiters (colons) rather than multiple periods, which forces Excel to treat the cell contents as text.

Solution 1Recommended

Use Valid Time Formats and Ensure Positive Results

Convert text-based time entries into a format Excel recognizes, apply a custom time format, and adjust your formula to prevent negative time outputs.

Excel stores dates and times as numeric values. When a time is entered with multiple periods (like 1.28.848), Excel cannot parse it as a number and defaults to text, which causes mathematical operations like subtraction to fail with a #VALUE! error.

Re-enter Time Values Correctly

Replace the first period with a colon. Enter the values as standard time fractions, for example, change '1.28.848' to '1:28.848'.

Apply Custom Time Formatting

Select the cells containing your times, press Ctrl+1 to open the Format Cells dialog, go to the 'Custom' category, and type '[h]:mm:ss.000' in the Type box. Click OK.

Calculate the Difference

In an empty cell, type your subtraction formula (e.g., '=B1-B2'). Ensure you are subtracting the smaller time from the larger time to yield a positive result.

Use Valid Time Formats and Ensure Positive Results

Handling Negative Times: Standard Excel time formatting does not support negative times and will display '########' if a time subtraction falls below zero. Always structure formulas to result in a positive duration.

Advanced Data Management

Calculate Time Differences Flawlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful cell formatting and formula calculation tools, making it easy to track durations down to the millisecond without encountering frustrating #VALUE! errors.

1. Open Your Data in WPS: Launch WPS Spreadsheet and open the workbook containing your time records.
2. Format the Cells: Select the time cells, right-click and choose 'Format Cells', then apply the '[h]:mm:ss.000' custom format.
3. Input the Formula: Enter a standard subtraction formula such as '=A2-B2' in the result cell.
4. Analyze Results: Drag the fill handle to apply the formula to other rows, instantly viewing precise, error-free time differences.

Easily apply custom time formats like [h]:mm:ss.000 for precise duration tracking.Fully compatible with Microsoft Excel formulas, cell formats, and .xlsx files.Lightweight, fast, and completely free to use for daily data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show ###### when I subtract times?

This happens when the result of your time subtraction is a negative number. Excel's default date and time system cannot display negative times, so it fills the cell with hash symbols. Ensure you subtract the earlier time from the later time.

How can I format time to show milliseconds in Excel?

Right-click the cell, select Format Cells, navigate to the Custom category, and input a format code like 'mm:ss.000' or '[h]:mm:ss.000' to display milliseconds accurately.

Can I calculate time differences that span past midnight?

Yes, you can use the MOD function to handle shifts across midnight. Use the formula '=MOD(End_Time - Start_Time, 1)' to calculate the correct duration.

Why are my numbers treated as text even after formatting?

If data is entered with invalid characters (like multiple decimal points) before formatting, Excel permanently stores it as text. You must correct the delimiter (e.g., replace periods with colons) to convert it back to a calculable number.

Excel & Data Problems