How to Format Excel Times with Milliseconds (e.g., 1:1.001)
Question details
The user needs to display time values with minutes, seconds, and milliseconds in a spreadsheet and ensure the underlying value can be successfully used in lookup formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Formatting specific time data down to the millisecond and referencing those time values inside a lookup table.
- Observed behavior
- Excel visually displays the custom time format (e.g., 1:1.001), but the formula bar retains the underlying serial time value (e.g., 12:01:01 AM), causing lookup formulas to fail or return errors.
Determine whether you only need to change how the time looks visually, or if you need the actual underlying cell value to match the displayed time string for data matching and lookup formulas.
Convert Time to Text Using the TEXT Function
Use this method if you need to use the time value in a lookup formula. It converts the underlying serial time data into a fixed text string that matches your desired display.
Because lookup formulas (like VLOOKUP or INDEX/MATCH) read the underlying cell value rather than the displayed formatting, using a custom cell format will often cause lookups to fail. The TEXT function permanently transforms the time into a readable string.
Click on an empty cell adjacent to the time value you want to convert.
Type =TEXT(A1, "m:ss.000") into the cell, assuming your original time value is in cell A1.
If your time tracks elapsed hours, adjust the formula to =TEXT(A1, "[h]:mm:ss.000").
Press Enter. The cell will now contain a pure text string representing the time with milliseconds, which can be safely referenced by your lookup tables without errors.
Apply Custom Number Formatting
Use this method if you only need to alter the visual display of the time on your spreadsheet without changing the underlying mathematical data.
Easily Format Times and Data with WPS Spreadsheet
WPS Spreadsheet provides powerful cell formatting tools and a full suite of formulas like TEXT, VLOOKUP, and XLOOKUP to handle your complex time data and lookups flawlessly.
- 1. Open your workbook in WPS: Launch WPS Office and open your spreadsheet containing the time data.
- 2. Format or convert: Select your cells and press Ctrl+1 to apply custom formats, or use the =TEXT() function exactly as you would in Excel.
- 3. Save and share: Save your file in the standard .xlsx format, ensuring perfect compatibility with colleagues using Microsoft Office.
Frequently Asked Questions
Why does my lookup formula return an error for custom formatted times?
Lookup formulas evaluate the underlying serial value of a cell, not its displayed format. Even if a cell visually displays '1:1.001', the formula bar might reveal the actual value is '12:01:01 AM'. You must use the TEXT function to convert the underlying value to a matching text string for the lookup to succeed.
What does the .000 mean in custom time formatting?
The '.000' suffix represents milliseconds in custom number formatting. Using three zeros forces the spreadsheet application to calculate and display the time accurately down to the thousandth of a second.
How do I format time to show cumulative hours over 24?
Wrap the hour code in square brackets, like [h]:mm:ss.000. This tells the spreadsheet to display total elapsed hours instead of resetting the hour count to zero after 24 hours have passed.
