Check timestamp text with milliseconds
Turn this text into a real date and time only if it has exactly three digits after the dot.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | DATEVALUE( … ) | Reads the first 10 characters as a date. | 11/4/2026 |
| 2 | TIMEVALUE( … ) | Reads the clock time and adds it. | … 2:30:15 PM |
| 3 | MID(A8,21,3)/86400000 | Adds the 3 digits after the dot. LET calls it all x. | … 2:30:15.123 PM |
| 4 | AND( … ) | Checks x writes back as the exact same text. | TRUE |
| 5 | IFERROR(LET( … )) | Shows x, or Review timestamp if anything fails. | 11/4/2026 2:30:15.123 PM |
A day has 86,400,000 milliseconds. 15.12 writes back as 15.012, so it fails.
=IFERROR(LET(x,DATEVALUE(LEFT(A8,10))+TIMEVALUE(MID(A8,12,8))+MID(A8,21,3)/86400000,IF(AND(YEAR(x)>=1901,TEXT(x,"yyyy-mm-dd""T""hh:mm:ss.000")=A8),x,"Review timestamp")),"Review timestamp")How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own timestamps. Paste timestamps like 2026-11-04T14:30:15.123 into the green column of the Template tab. The dates and times fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Keep the original text too. Excel stores times with tiny rounding, so the text is the exact record.
Microsoft's help: TEXT function
OTHER ROUTES YOU MIGHT LIKE

Clean a mixed column of clock times
Text 9:05 AM → 9:05:00 AM; timestamps keep their time
Get the template →
Keep real timestamps, check the text
2026-11-04T14:30:15 → November 4, 2026 2:30:15 PM; real timestamps stay
Get the template →
Rewrite clock text in a chosen format
2:30:15 PM with hh:mm:ss → 14:30:15
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →