Omit a known missing timestamp
Leave a missing marker like 0001-01-01T00:00:00 blank. Read the other timestamps.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | OR(A8="",A8=B8) | Checks for an empty cell or the missing marker. | No |
| 2 | DATEVALUE( … ) | Reads the first 10 characters as a date. | 11/4/2026 |
| 3 | TIMEVALUE( … ) | Adds the clock time after the T. | 11/4/2026 14:30:15 |
| 4 | IFERROR( … ) | Shows Review timestamp if Excel can't read it. | 11/4/2026 14:30:15 |
| 5 | IF( … ) | Leaves the cell blank if part 1 says yes. | 11/4/2026 14:30:15 |
Read it left to right. Only the marker you type in column B is left blank.
=IF(OR(A8="",A8=B8),"",IFERROR(DATEVALUE(LEFT(A8,10))+TIMEVALUE(MID(A8,12,8)),"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 into the green Timestamp text column of the Template tab. Put your missing marker next to it. 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
Only type the marker your file really uses for missing times. A real date that matches it is left blank too.
Microsoft's help: IF function
OTHER ROUTES YOU MIGHT LIKE

Read exact four- or six-digit clock codes
143015 → 2:30:15 PM and 2330 → 11:30:00 PM
Get the template →
Read a time only if it fits a pattern
14:30, pattern hh:mm → 2:30:00 PM
Get the template →
Check and read 2026-11-04T14:30:15
2026-11-04T14:30:15 → November 4, 2026 2:30:15 PM, or Review timestamp
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →