Read interval codes as elapsed time
Turn a CIM interval like 00000002013015.123456:000 into 49:30:15, not a date.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | VALUE(LEFT(A8,8)) | Reads the first 8 digits as whole days. | 48:00:00 |
| 2 | VALUE(MID(A8,9,2))/24 | Adds the 2 hour digits. | 49:00:00 |
| 3 | VALUE(MID(A8,11,2))/1440 | Adds the 2 minute digits. | 49:30:00 |
| 4 | VALUE(MID(A8,13,2))/86400 | Adds the 2 second digits. | 49:30:15 |
| 5 | VALUE(MID(A8,16,6))/86400000000 | Adds the 6 digits after the dot as part of a second. | 49:30:15.123456 |
Excel counts time in days, so each piece becomes part of a day. The :000 ending marks an interval, not a date.
=VALUE(LEFT(A8,8))+VALUE(MID(A8,9,2))/24+VALUE(MID(A8,11,2))/1440+VALUE(MID(A8,13,2))/86400+VALUE(MID(A8,16,6))/86400000000How 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 codes. Paste codes like 00000002013015.123456:000 into the green column of the Template tab. The elapsed times fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
The cell shows whole seconds, but the 6 digits after the dot are kept. Copy results into cells formatted [h]:mm:ss.
Microsoft's help: VALUE function
OTHER ROUTES YOU MIGHT LIKE

Read 20261104143015.0Z timestamps
20261104143015.0Z → November 4, 2026 2:30:15 PM
Get the template →
Read UTC times with two-digit years
261104143015Z → November 4, 2026 2:30:15 PM
Get the template →
Read long Unix codes, even before 1970
Unix microseconds -1361957384876544 → 11/4/1926 2:30:15.123 PM
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →