Convert UTC timestamp to Excel datetime

UTC timestamps like 2026-01-18T08:00:00Z are a common standard for representing dates and times, but Excel won't correctly recognize this format without some help. If you try to apply date formatting to a UTC timestamp, nothing happens.

In this example, the goal is to convert UTC timestamps to datetimes that Excel can recognize. In addition, we'll look at how to convert UTC timestamps to datetimes in other timezones.

Table of contents

• About UTC timestamps
• About Excel datetimes
• Entering datetimes in Excel
• Converting UTC timestamps with SUBSTITUTE
• Converting UTC timestamps with TEXTSPLIT
• Converting UTC timestamps to other time zones
• Summary

About UTC timestamps

UTC timestamps look like this: "2026-01-18T08:00:00Z". This format is a text value that conforms to the ISO 8601 standard for representing dates and times. The generic format is "YYYY-MM-DDTHH:MM:SSZ". This is a common format for representing dates and times in many systems, but Excel doesn't recognize it as a date by default. If you place a UTC timestamp in a cell, Excel will display it as a text value. If you try to apply a date or time formatting to a UTC timestamp, nothing will happen.

About Excel datetimes

In Excel, dates are large serial numbers starting on January 1, 1900. The date January 1, 1900 is the serial number 1, January 2, 1900 is the serial number 2, and so on. The date January 1, 2026 is represented as the serial number 46023. Excel times are fractions of a day. The time 12:00 AM is represented as the decimal value 0. The time 12:00 PM is the decimal value 0.5. The time 6:00 PM is the decimal value 0.75. Although many users don't realize it, dates and times are often stored as a single number in a cell. For example, January 18, 2026 12:00 PM is stored in Excel as the number 46040.5. This is referred to as a "datetime" value - a single number that represents both a date and a time. Because the UTC timestamp contains both a date and a time, our goal is to convert the UTC timestamp to a datetime value that Excel can recognize.

Entering datetimes in Excel

One way to enter a datetime in Excel is to use a formula based on the DATE and TIME functions. For example, to enter the datetime January 18, 2026 at 12:00 PM, you can use a formula like this:

=DATE(2026,1,18)+TIME(12,0,0)

This formula returns the datetime value January 18, 2026 at 12:00 PM (46040.5). Of course, most users will enter the date and times manually by typing the date and time in a cell. The trick is to enter the date and time separated by a space. For example, to enter the datetime January 18, 2026 at 12:00 PM, you can enter 18-Jan-2026 12:00 PM. Excel will automatically convert the text to a datetime value. Importantly, you can also enter a datetime in the ISO 8601 format by removing the "T" and "Z".

2026-01-18 12:00:00 // YYYY-MM-DD HH:MM:SS

Excel will correctly understand this as a datetime value. In other words, if we remove the "T" and "Z" from the UTC timestamp, Excel will be able to interpret it as a valid datetime. This is the approach we will take in the following examples.

Tip: you can also enter a format like 1/18/2026 12:00 PM or 18/1/2026 12:00 PM depending on your regional settings. However, this can be confusing and ambiguous and result in incorrect dates. A format like 2026-01-18 12:00 PM or 2026-01-18 12:00 avoids this confusion.

Converting UTC timestamps with SUBSTITUTE

Since we want to remove the 'T' and the 'Z' from the UTC timestamp, one solution is to use the SUBSTITUTE function. The SUBSTITUTE function replaces text in a text string with another text string. To replace the "T" with a space, and to remove the "Z" altogether, we can use SUBSTITUTE like this:

=SUBSTITUTE(B5,"T"," ") // replace the "T" with a space
=SUBSTITUTE(B5,"Z","") // remove the "Z"

Note in the second formula, we are removing the "Z" altogether by using an empty string (""). By nesting the two SUBSTITUTE functions together, we can remove both the "T" and the "Z" in a single formula:

=SUBSTITUTE(SUBSTITUTE(B5,"T"," "),"Z","") // remove both the "T" and the "Z"

The inner SUBSTITUTE function replaces the "T" with a space and hands the result to the outer SUBSTITUTE function, which then replaces the "Z" with an empty string. The final result is the text string "2026-01-18 08:00:00" without the "T" and "Z".

You might think we are done at this point, but we aren't quite finished. If you use the formula above, Excel will simply return the text string without recognizing it as a date/time value. The final step is to give Excel a little kick in the butt to make it evaluate the text string as a number. One trick is to use the double negative operator (--):

=--SUBSTITUTE(SUBSTITUTE(B5,"T"," "),"Z","")

This is the final formula in cell D5. The double negative (--, also called double unary) is a simple math operation that causes Excel to try to interpret the text string as a number. The result is the datetime value January 18, 2026 at 8:00 AM (46040.3333333333).

Excel's laziness here isn't really surprising. SUBSTITUTE is a text function, and it returns a text string, so Excel leaves it alone. The only thing worse than a lazy Excel is a hyper Excel that converts values without asking.

Converting UTC timestamps with TEXTSPLIT

Of course, since this is Excel, there is always another way to solve the problem. Another way to convert the UTC timestamp to a datetime value is to use the TEXTSPLIT function. TEXTSPLIT splits a text string into an array of values based on a delimiter. One interesting feature of TEXTSPLIT is that the delimiter itself is lost in the process. For example, if we use TEXTSPLIT to split the UTC timestamp with the "T" as the delimiter, we get an array with two separate values, the date and the time.

=TEXTSPLIT(B5,"T") // returns {"2026-01-18","08:00:00Z"}

Note that the "T" is lost in the process. We can actually go one step further and split the UTC timestamp with the "T" and "Z" as the delimiters in a single formula:

=TEXTSPLIT(B5,{"T","Z"},,1) // returns {"2026-01-18","08:00:00"}

Note that we have provided the delimiters as an array constant and also set ignore_empty to 1 (TRUE). We need to set ignore_empty to TRUE to prevent TEXTSPLIT from returning an array with three values: {"2026-01-18","08:00:00",""}, which comes from the "Z" delimiter.

Okay, so at this point, the formula above will split the UTC timestamp into two values: the date and the time. How can we bring them back together again? Since our goal is to get a single numeric value in the end, it's actually pretty simple. We can simply wrap the formula in the SUM function like this:

=SUM(--TEXTSPLIT(B5,{"T","Z"},,1))

Notice we are using the double unary operator (--) trick again to convert the text strings returned by TEXTSPLIT to numbers before adding them together. The result is the datetime value January 18, 2026 at 8:00 AM (46040.3333333333).

Is this formula better than the SUBSTITUTE formula? Well, it's certainly more clever, but it's also more complex. But I think it is also a good learning formula because it brings together many important concepts at once. It also manages the date and time separately, which might be convenient in some situations.

Converting UTC timestamps to other time zones

In the previous examples, we converted the UTC timestamp to a datetime value in the UTC timezone. But what if you need to convert the UTC timestamp to a datetime value in a different timezone? For example, what if you need to convert the UTC timestamp to a datetime value in the Pacific Standard Time (PST) timezone?

To do this, we need to know the offset between the UTC timezone and the target timezone. For example, the offset between the UTC timezone and the Pacific Standard Time (PST) timezone is -8 hours. This means that when the UTC timestamp is 8:00 AM, the Pacific Standard Time (PST) timestamp is 12:00 AM. The offset is negative because the Pacific Standard Time (PST) timezone is 8 hours behind the UTC timezone.

To convert the UTC timestamp to a datetime value in the Pacific Standard Time (PST) timezone, we can use either of the following formulas, based on the previous examples:

=--SUBSTITUTE(SUBSTITUTE(B5,"T"," "),"Z","") - TIME(8,0,0)
=SUM(--TEXTSPLIT(B5,{"T","Z"},,1)) - TIME(8,0,0)

In the screenshot below, we have converted the UTC timestamp to a datetime value in the Pacific Standard Time (PST) timezone with the TEXTSPLIT formula:

Notice we are using the TIME function to create a time value of 8 hours. Then, we simply subtract this time value from the result to get the datetime value in the Pacific Standard Time (PST) timezone. To convert to Eastern Standard Time (EST), which is UTC-5, we can simply subtract 5 hours from the result:

=--SUBSTITUTE(SUBSTITUTE(B5,"T"," "),"Z","") - TIME(5,0,0)
=SUM(--TEXTSPLIT(B5,{"T","Z"},,1)) - TIME(5,0,0)

Summary

UTC timestamps are a common format for dates and times in data exports, APIs, and other systems. They look like this: "2026-01-18T08:00:00Z". The problem is that Excel doesn't recognize this format as a date. If you try to apply date formatting to a UTC timestamp, nothing happens. Fortunately, if you strip out the "T" and "Z", Excel will recognize what's left as a valid datetime. The SUBSTITUTE approach is simple and works in all versions of Excel:

=--SUBSTITUTE(SUBSTITUTE(B5,"T"," "),"Z","")

The TEXTSPLIT approach is a bit more clever, and a good way to learn about delimiters and array handling:

=SUM(--TEXTSPLIT(B5,{"T","Z"},,1))

Both formulas use the double negative (--) trick to force Excel to evaluate the text as a number. Once you have a proper datetime value, you can format it however you like, and adjust for timezone offsets by adding or subtracting hours with the TIME function.