Read minute-offset timestamps as UTC

Turn a CIM code like 20261104143015.123456+330 into UTC: 11/4/2026 9:00:15.123 AM.

What you'll make

In Excel: 20261104143015.123456+330 becomes 11/4/2026 9:00:15.123 AM.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1DATE( … )Reads the year, month and day as a real date.11/4/2026
2TIME( … )Adds the hour, minutes and seconds.11/4/2026 2:30:15 PM
3VALUE( … )/86400000000Adds the 6 digits after the dot as part of a second.… 2:30:15.123 PM
4IF( … )Gives 1 for a plus sign, -1 for minus.1
5VALUE( … )/1440Turns the last 3 digits from minutes into part of a day.… 9:00:15.123 AM

The last 3 digits are minutes, so +330 is 5 hours 30 minutes. A plus offset comes off; a minus offset is added.

=DATE(LEFT(A8,4),MID(A8,5,2),MID(A8,7,2))+TIME(MID(A8,9,2),MID(A8,11,2),MID(A8,13,2))+VALUE(MID(A8,16,6))/86400000000-IF(MID(A8,22,1)="+",1,-1)*VALUE(RIGHT(A8,3))/1440

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 20261104143015.123456+330 into the green column of the Template tab. The UTC dates and 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 results are UTC, and the cell shows 3 of the 6 digits after the dot. Keep the original codes beside them.

Microsoft's help: DATE function