Formula Errors

Fix Excel Elapsed-Time Formulas That Miss Two-Minute Values

Huda QurayshiHuda Qurayshi Nov 20, 2026 869 views

Question details

The user needs an Excel formula to recognize elapsed times of one or two minutes reliably.

Fix Excel Elapsed-Time Formulas That Miss Two-Minute Values
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet compares the elapsed time between two cells and returns a result for one- or two-minute intervals.
Observed behavior
Some intervals displayed as exactly two minutes do not match the comparison because their underlying fractional time values or formats differ.
Before you start

Confirm that F10 and F11 contain valid Excel times or date-time values, and that F11 is later than F10. Check whether you need to compare actual elapsed seconds or the rounded minutes shown on screen.

Solution 1Recommended

Compare elapsed time rounded to the nearest second

Use this method when the elapsed interval should count as one or two minutes to the nearest second.

Excel stores time as a fraction of a day. Multiplying the difference by 86,400 converts it to seconds, and rounding helps avoid small fractional differences.

1
Inspect the elapsed time

In an empty cell, enter =F11-F10. Use Home > Number Format > More Number Formats and apply [h]:mm:ss to display the elapsed hours, minutes, and seconds.

2
Enter the comparison formula

In the result cell, enter =IFERROR(IF(OR(ROUND((F11-F10)*86400,0)=60,ROUND((F11-F10)*86400,0)=120),1,"-"),"-"). Adjust the cell references if your times are elsewhere.

3
Check the result

Review the result cell: it returns 1 when the rounded interval is 60 or 120 seconds, and a dash otherwise.

Compare elapsed time rounded to the nearest second
Rounding and overnight times: This compares to the nearest second, so an interval close to 120 seconds may also match. For time-only values crossing midnight, use MOD(F11-F10,1) in place of F11-F10; this assumes the elapsed interval is less than 24 hours.
Work with elapsed times in WPS Spreadsheets

Compare Elapsed Times in WPS Spreadsheets

Open your workbook in WPS Spreadsheets and compare elapsed times using a formula that rounds the difference to seconds.

  1. 1. Open the workbook: Open WPS Spreadsheets, select Open, and choose your Excel workbook.
  2. 2. Check the time cells: Locate the start and end time cells, such as F10 and F11, and confirm that they contain valid times or date-time values.
  3. 3. Add the formula: In an empty result cell, enter =IFERROR(IF(OR(ROUND((F11-F10)*86400,0)=60,ROUND((F11-F10)*86400,0)=120),1,"-"),"-").
  4. 4. Review and save: Check that the result is 1 for intervals that round to 60 or 120 seconds, then use File > Save to save the workbook.
Compare one- and two-minute intervals with a seconds-based formulaOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and number formats
microsoft office alternative - wps office

Frequently Asked Questions

Why does a time displayed as 0:02 fail an Excel comparison?

The cell format may round the display while the stored value contains additional seconds or fractions. Check the difference with a seconds format or compare the value after rounding it to seconds.

Does TEXT(F11-F10,"h:mm") test for exactly two minutes?

No. It creates a rounded minute display as text, so values other than exactly two minutes can appear as 0:02. Compare the elapsed value in seconds when the precise interval matters.

How do I handle an elapsed time that crosses midnight?

If F10 and F11 contain time-only values and the interval is less than 24 hours, use MOD(F11-F10,1) for the difference. If the cells contain dates as well as times, subtract the earlier date-time from the later one.

Why does my comparison with 0:02 still fail?

Check whether the comparison cell contains a numeric time or text. TEXT returns text, so compare its result with "0:02"; a numeric time should instead be compared as a numeric value.