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

In Excel: 00000002013015.123456:000 becomes 49:30:15, not a date.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1VALUE(LEFT(A8,8))Reads the first 8 digits as whole days.48:00:00
2VALUE(MID(A8,9,2))/24Adds the 2 hour digits.49:00:00
3VALUE(MID(A8,11,2))/1440Adds the 2 minute digits.49:30:00
4VALUE(MID(A8,13,2))/86400Adds the 2 second digits.49:30:15
5VALUE(MID(A8,16,6))/86400000000Adds 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))/86400000000

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. 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.
  3. 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