Fix Excel Time Subtraction Errors for Times with Milliseconds
Question details
The user needs to subtract two time values that include milliseconds without receiving a #VALUE! error.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Subtracting timestamps such as 18:13:35.033 and 18:13:03.438 to calculate an elapsed duration.
- Observed behavior
- Excel returns #VALUE! when the timestamps include milliseconds, although subtraction works for times without milliseconds.
Check whether the timestamp cells are aligned as text or as numeric time values. Also confirm the decimal separator required by your Windows regional settings, because some locales use a comma instead of a period.
Convert timestamp text to Excel time values before subtracting
Use this method when imported timestamps are stored as text and direct subtraction returns #VALUE!.
Excel can subtract times only when both cells contain numeric serial time values. Converting the hours, minutes, seconds, and milliseconds explicitly avoids locale-related text parsing issues.
Select a timestamp cell and check its alignment or use the formula =ISTEXT(A1). If it returns TRUE, the value must be converted before subtraction.
In a helper cell, enter =(LEFT(A1,2)*3600+MID(A1,4,2)*60+MID(A1,7,2)+MID(A1,10,3)/1000)/86400 for a timestamp in the HH:MM:SS.fff format.
Apply the same formula to the second timestamp cell, replacing A1 with its cell reference, such as B1.
In an elapsed-time cell, enter =D1-C1, where C1 and D1 contain the converted start and end times.
Select the result cell, open Format Cells, choose Custom, and use [h]:mm:ss.000 to display hours, minutes, seconds, and milliseconds.
Use the decimal separator required by your regional settings
Use this method when the timestamps are entered manually and Excel recognizes a comma, rather than a period, as the decimal separator.
Calculate with milliseconds as whole numbers
Use this approach when your source system exports elapsed times or timestamps as integer milliseconds.
Calculate Millisecond Time Differences in WPS Spreadsheets
WPS Spreadsheets lets you convert imported timestamp text into numeric time values, subtract timestamps, and format elapsed results with milliseconds using familiar spreadsheet formulas and formatting tools.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the timestamp columns.
- 2. Create a converted-time helper column: Click an empty cell beside the timestamp, then enter =(LEFT(A1,2)*3600+MID(A1,4,2)*60+MID(A1,7,2)+MID(A1,10,3)/1000)/86400.
- 3. Fill the conversion formula down: Select the helper cell and drag its fill handle down to convert all timestamp entries in the column.
- 4. Subtract the converted timestamps: Click the result cell and enter =D1-C1, using the two helper cells that contain the converted start and end times.
- 5. Set a millisecond display format: Select the result cells, choose Home > Number Format > More Number Formats, select Custom, and enter [h]:mm:ss.000.
Frequently Asked Questions
Why does Excel return #VALUE! when I subtract times with milliseconds?
The timestamp cells are commonly stored as text rather than numeric time values, or the millisecond separator does not match your regional settings. Convert the text first or enter the locale-specific decimal separator.
How do I display an Excel time difference with milliseconds?
Select the result cell, open Format Cells, choose Custom, and enter [h]:mm:ss.000. The square brackets allow hours to continue beyond 24 when needed.
Should I use a period or a comma before milliseconds in an Excel time?
Use the decimal separator configured for your regional settings. Many English-language regions use 18:13:35.033, while regions using a comma require 18:13:35,033.
What formula converts a text time in HH:MM:SS.fff format to an Excel time value?
For a value in A1, use =(LEFT(A1,2)*3600+MID(A1,4,2)*60+MID(A1,7,2)+MID(A1,10,3)/1000)/86400. It converts the text components into a fraction of one day.
Can I calculate time differences by using integer milliseconds instead?
Yes. Subtract the integer millisecond values, then divide the result by 86400000 to convert it to spreadsheet time. Format the result as [h]:mm:ss.000.
