Fix Excel Elapsed-Time Formulas That Miss Two-Minute Values
Question details
The user needs an Excel formula to recognize elapsed times of one or two minutes reliably.

- 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.
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.
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.
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.
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.
Review the result cell: it returns 1 when the rounded interval is 60 or 120 seconds, and a dash otherwise.

Compare elapsed time as formatted minute text
Use this method when the displayed minute value, rather than the exact elapsed seconds, determines the result.
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. Open the workbook: Open WPS Spreadsheets, select Open, and choose your Excel workbook.
- 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. 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. 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.

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.




