Convert UTC Timestamps to Local Time in Excel

Marchwood Freight's out-of-hours desk answered 286 incidents in 2026, and the service review billed 23 of them as breaches of the 30-minute target. Four were real. The other nineteen were one hour, added by a subtraction that crossed a time zone.

The ticketing tool exports the moment an incident was raised in UTC: 2026-03-29T01:12:00Z, written as text, with a T in the middle and a Z on the end. The phone system exports the moment it was answered in London time.

Nobody noticed, because for five months of the year the two clocks agree. London is UTC+0 from late October to late March, so every winter row subtracts correctly and every summer row comes out an hour too long.

An hour is not a rounding error against a thirty-minute target. It is twice the target, applied to every incident raised between the last Sunday in March and the last Sunday in October.

01Two Clocks in One Subtraction

Column B is what the ticketing tool wrote: an ISO 8601 timestamp in UTC, as text. Column E is what the phone system wrote, in London time. Column F subtracts one from the other and calls the answer a response time.

On the summer rows that subtraction is sixty minutes too generous, because the answer was recorded on a clock an hour ahead of the question. INC-2044 took 19 minutes and was billed as 79.

Columns C and D are the second half of the problem. C converts UTC to local with a fixed =B2+1/24, which is right in summer and an hour early on every winter night. D applies the rule the clocks actually follow.

02A UTC Stamp Is Text Until You Parse It

2026-03-29T01:12:00Z is not a time to Excel. It is twenty characters, left-aligned in the cell, and subtracting it from anything returns #VALUE! rather than a duration.

The stamp has a fixed shape, so the parse is two functions and nothing else:

=DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,12,8))

LEFT takes 2026-03-29 and DATEVALUE turns it into a serial number. MID takes the eight characters after the T and TIMEVALUE turns them into a fraction of a day. Added together they are one real Excel timestamp.

The T and the Z have to go, however you do it. =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"T"," "),"Z","")) does it in one cell, and TEXTBEFORE and TEXTAFTER do it where the seconds are sometimes missing and the fixed character positions cannot be trusted.

A parsed column is right-aligned and a text column is not, which is the fastest check on the screen. =COUNT(B2:B287) is the slower and better one: it counts numbers only, so anything under 286 is a stamp that never converted.

03An Offset Is a Fraction of a Day

Once the stamp is a number, converting it is arithmetic. A day is 1, an hour is 1/24, a minute is 1/1440, and an offset is added or subtracted like any other number.

TIME(5,45,0) is the safer way to write an awkward offset, because it saves anyone from deciding what 5:45 is in decimal hours. Half an hour is 0.5/24 and three quarters is 0.75/24; writing 5.45/24 is a 27-minute error nobody ever spots.

The display is a separate decision from the value. A converted cell keeps whatever number format it inherited, so a correct timestamp can still read as 46110.05, or as a time of day with the date hidden.

04The Five Months a Fixed +1 Is Wrong

London is not UTC+1. It is UTC+0 for about five months of the year and UTC+1 for the other seven, so a column built on =B2+1/24 runs an hour ahead for every winter row in the file.

An hour is survivable in the middle of the afternoon. It is not survivable near midnight, which is exactly where an out-of-hours desk does its work.

INC-2041 was raised at 23:41 UTC on Saturday 28 March, while the clocks still said GMT. The fixed offset filed it at 00:41 on the 29th, so it left the Saturday night count and joined Sunday's.

Nine of the 286 incidents changed date that way, and two of the nights they moved on to crossed the five-incident threshold that triggers a review.

05Where the Clocks Change, in a Formula

The United Kingdom and the European Union move on the same two instants: 01:00 UTC on the last Sunday in March, and 01:00 UTC on the last Sunday in October.

"Last Sunday in March" is a formula, not a date somebody retypes every year:

Starts  =DATE(A2,3,31)-WEEKDAY(DATE(A2,3,31))+1
Ends    =DATE(A2,10,31)-WEEKDAY(DATE(A2,10,31))+1

WEEKDAY with no second argument returns 1 for a Sunday and 7 for a Saturday. Subtracting it from the 31st and adding one lands on the last Sunday of that month: 29 March and 25 October in 2026.

Add 1/24 to each result and you have the two UTC instants the offset changes on. Put them in cells named BSTStart and BSTEnd, and every conversion in the workbook can read the same two numbers.

Scope. These two formulas are the European rule and nothing else. The United States changes on the second Sunday in March and the first Sunday in November, at 02:00 local, and the southern hemisphere runs the opposite way round: Sydney goes forward in October and back in April.

Japan, India, China and most of Africa never change at all, and Arizona and Saskatchewan sit out a change their neighbours make. Section 10 is what to do when one log holds several of them.

06The Conversion That Knows Which Hour It Is

With the two boundaries in cells, the offset stops being a constant and becomes a test: =B2+(AND(B2>=BSTStart+1/24,B2<BSTEnd+1/24))/24.

The comparison returns TRUE or FALSE, Excel reads those as 1 and 0, and dividing by 24 turns the answer into one hour or nothing at all. One formula, both seasons, no list of dates to maintain.

LET says the same thing out loud when the boundaries are worked out in place rather than named:

=LET(utc, B2,
     y, YEAR(utc),
     starts, DATE(y,3,31)-WEEKDAY(DATE(y,3,31))+1+1/24,
     ends, DATE(y,10,31)-WEEKDAY(DATE(y,10,31))+1+1/24,
     utc + (AND(utc>=starts, utc<ends))/24)

=B2+IF(AND(B2>=BSTStart+1/24,B2<BSTEnd+1/24),1,0)/24 is the same test written out in full, for anyone who mistrusts a bare TRUE doing arithmetic. Excel reads the two identically.

Both comparisons are against UTC, never against the local column. Testing a local stamp against a local boundary is circular: the hour you are trying to work out is the hour you would need in order to run the test.

07Never Subtract Across Two Zones

The £3,450 came out of one formula: =(E2-B2)*1440, the minutes between the raised stamp and the answered stamp. It is correct arithmetic on two numbers that are not measured from the same origin.

From late March to late October, E is an hour ahead of B before anybody does anything, so the subtraction hands back the real response plus sixty minutes. Nineteen incidents crossed the 30-minute line on that hour alone.

The fix is a rule rather than a formula: convert everything to UTC on the way in, do every subtraction there, and convert to local only to display it. A duration has no time zone, so the moment two zones meet inside one subtraction the answer is wrong by the difference between them.

INC-2047 is the row that makes the point. It is a real breach, in November, on a night when the two clocks agreed — and it spent the review buried in a list of twenty-three where nineteen were an artefact of arithmetic.

08The Hour That Happens Twice

At 02:00 local on 25 October the clocks go back to 01:00, so 01:30 local happens twice that night: once at 00:30 UTC, and again an hour later at 01:30 UTC.

A local timestamp inside that hour is genuinely ambiguous. Nothing in the cell says which of the two it was and no formula can recover it, which is why a log kept in local time has already lost something a UTC log never had to store.

March does the opposite. Between 01:00 and 02:00 local on 29 March there is no time at all — the clocks jump from 00:59 to 02:00 — so a local stamp of 01:30 that night is not an error you can correct, only one you can flag.

Ambiguous   =AND(D2>=BSTEnd+1/24,   D2<BSTEnd+2/24)
Impossible  =AND(D2>=BSTStart+1/24, D2<BSTStart+2/24)

INC-2046 is in the ambiguous hour: 01:30 local on 25 October. Its UTC stamp is the only reason we know which 01:30 it was. Run both tests over the local column and the rows that come back TRUE are the ones to read from the source, not from the sheet.

09Reporting by Local Day Without Moving Rows

The client reads a count per night in their own time, so the report has to be local even though the arithmetic is not. That is one more column, not a second version of the log.

=INT(D2) strips the time and leaves the local date. Counting on that column is honest; counting on INT of the UTC column files the last hour of every winter night under tomorrow.

Local day     =INT(D2)
Hour of night =HOUR(D2)
Incidents     =COUNTIFS($I$2:$I$287,J2)
Minutes       =SUMIFS($G$2:$G$287,$I$2:$I$287,J2)
Average       =ROUND(AVERAGE(FILTER($G$2:$G$287,$I$2:$I$287=J2)),1)

HOUR on the same column gives the hour-of-night profile the client asks for next, and it goes wrong the same way if you point it at the UTC column instead.

A date criterion has to reach COUNTIFS as a number, which is exactly what INT gives you. Typing "25/10/2026" into the criterion works on a machine whose regional settings agree with yours and returns a quiet zero on a colleague's.

10When the Log Holds More Than One Country

One desk is one rule. Three desks are a table, because the hemispheres disagree about which months are summer and the Americas change on different Sundays from Europe.

Hold the base offset and the two boundaries in a small table and read them per row: =XLOOKUP($C2,Sites[Site],Sites[Offset]) returns the base, and the same lookup against the boundary columns feeds the test. INDEX and MATCH do the job identically where XLOOKUP is not available.

Excel has no time zone database. No function takes a city and an instant and returns the offset in force, so anything you build is a table a person has to maintain — which is the honest argument for converting before the data reaches the sheet at all.

11Writing a Timestamp Back Out

Anything leaving the sheet for another system goes out as UTC in ISO 8601, because it is the one spelling no reader has to guess at:

=TEXT(utc,"yyyy-mm-dd") & "T" & TEXT(utc,"hh:mm:ss") & "Z"

Building it from two TEXT calls keeps the literal T and Z outside the format string, where they would need escaping and would still catch out the next person to edit the cell.

NOW() is not UTC. It reads the machine's clock, so a "received at" stamp written by a London laptop in July sits an hour ahead of one written by the same formula in January, and neither cell says which. Convert it at the moment you capture it, with the same boundary test as every other row.

12Power Query, When the File Lands Every Week

A log that arrives weekly should be converted on import rather than in a column somebody remembers to fill down. Power Query reads ISO text into a datetimezone type and converts it in one step.

What you need. DateTimeZone.FromText reads the stamp with its offset, DateTimeZone.SwitchZone moves it to a fixed offset, and DateTimeZone.ToLocal moves it to the machine's own zone with daylight saving applied. That last one depends on the machine, which is fine on a desktop and a trap on a refresh running in the cloud.

A query step also survives a column being inserted, because it refers to a column by name rather than by address. Three of the four bugs in this article are address-shaped.

The sheet-side formulas are still worth knowing. They are what you reach for when the file is already open, when a colleague mails you twenty rows, and when you have to show a client exactly where their number came from.

13Eight Things That Bite

1. A fixed +1/24. Right for seven months and an hour early for five, and it does its worst damage on the rows nearest midnight.
2. Subtracting a local stamp from a UTC one. The result is out by the offset every time, and it looks exactly like a plausible duration.
3. Leaving the stamp as text. #VALUE! is the good outcome; a quiet zero inside a SUMIFS is the bad one.
4. NOW() treated as a UTC clock. It is the machine's clock, and the machine moves with the local rules twice a year.
5. Testing the boundary against local time. Circular: the answer you need is the thing being tested.
6. A date criterion typed as text. "25/10/2026" reads differently on a machine set to US order; pass INT of the timestamp or a DATE instead.
7. Treating 01:30 on a clocks-back night as one instant. It is two, and only the exporting system knows which.
8. An offset table nobody owns. Governments move these dates, and a table written once is wrong the first year one of them does.

14Mini Exercises

1. Parse row 2 with =DATEVALUE(LEFT(B2,10))+TIMEVALUE(MID(B2,12,8)) and format it dd/mm/yyyy hh:mm. Expect 28/03/2026 23:41.
2. Write the two 2026 boundaries with the WEEKDAY formulas from section 5. Expect 29 March and 25 October.
3. Convert all eight rows with the AND test and compare against column D. Expect every row to match D, and four rows to disagree with C.
4. Recalculate the response times from two UTC stamps. Expect the 23 breaches to fall to 4 and the longest to fall from 86 minutes to 34.
5. Flag the ambiguous hour with =AND(D2>=BSTEnd+1/24,D2<BSTEnd+2/24). Expect TRUE on INC-2046 alone, because 01:30 local on 25 October happened twice.

What to Take Away

Nothing in this file was mistyped. Every stamp was accurate, every subtraction was arithmetic anybody would write, and the bill they produced between them was five times the one the contract called for.

The error was that two columns measured time from two different origins and nothing on the sheet said so. A timestamp with no zone attached is a number with no units, and Excel will subtract one from the other without a murmur.

So keep one rule and the rest follows: the log is UTC, the report is local, and the conversion happens in exactly one column. Nineteen of those twenty-three breaches would never have reached a page.