Convert Time Zones in Excel With a Simple Formula
By ExcelReady TeamUpdated 6 min read
The short answer
To convert time zones in Excel, add or subtract the hour difference as a fraction of a day: =A2+5/24 moves a time 5 hours later, =A2-8/24 moves it 8 hours earlier. Use decimals for half-hour zones, like 5.5/24 for India. Excel has no time zone database, so you must update offsets yourself when daylight saving changes.
Key takeaways
- Excel stores one day as 1, so one hour is 1/24. Converting time zones is just adding hours/24.
- Keep the offset in a cell and lock it with $ so one formula converts a whole column.
- Store full date-times, not just times, so the date rolls over correctly past midnight.
- For time-only values, wrap the result in MOD(...,1) to keep it between 00:00 and 23:59.
- Excel doesn't know about daylight saving. Check offsets for the exact date you are converting.
On this page+
To convert time zones in Excel, add or subtract the time difference as a fraction of a day: =A2+8/24 turns 20:00 UTC into 04:00 the next morning in Singapore. Excel has no built-in time zone function, but because it stores times as numbers, a simple formula does the job. This guide is for anyone who schedules calls, reads system logs in UTC or reports across offices, and it covers half-hour zones, midnight and daylight saving.
How Excel stores times (and why /24 works)#
Excel stores every date as a whole number of days and every time as a fraction of a day. Noon is 0.5, 06:00 is 0.25, and one hour is 1/24. A date with a time, like 15 January 2026 at 20:00, is one number: the day count plus 20/24.
| What you want | Add this | Why |
|---|---|---|
| 1 hour later | +1/24 | One hour is 1/24 of a day |
| 8 hours later | +8/24 | Eight of those hours |
| 5 hours earlier | -5/24 | Subtract to go back |
| 5 hours 30 minutes later | +5.5/24 | Half an hour is 0.5 hours |
So converting a time zone is just adding hours. If the number-as-date idea is new, read why Excel dates are really numbers, or practise date basics in a live sheet.
How to convert time zones in Excel with one formula#
To convert a date-time from one zone to another, add the difference in hours divided by 24. Say A2 holds a meeting at 15 Jan 2026, 20:00 UTC (Coordinated Universal Time, the global reference clock):
| City | UTC offset in January | Formula | Result |
|---|---|---|---|
| London | 0 | =A2+0/24 | 15 Jan 20:00 |
| New York | -5 | =A2-5/24 | 15 Jan 15:00 |
| Mumbai | +5.5 | =A2+5.5/24 | 16 Jan 01:30 |
| Singapore | +8 | =A2+8/24 | 16 Jan 04:00 |
| Sydney | +11 | =A2+11/24 | 16 Jan 07:00 |
Notice that the date moves forward for Mumbai, Singapore and Sydney. That happens automatically because A2 holds a full date and time. If the result shows a long decimal, apply a date-time format: press Ctrl+1 (Windows) or ⌘1 (Mac), choose Custom and type d mmm yyyy hh:mm.
You can also write the offset with TIME, such as =A2+TIME(8,0,0) or =A2+TIME(5,30,0). TIME can't take a negative result, so for zones behind you, subtract instead: =A2-TIME(5,0,0). The /24 version handles both directions and decimals, which is why most people use it. The guide on adding hours to a time covers TIME in more detail.
Convert between two zones that aren't UTC#
When neither zone is UTC, subtract the source offset from the target offset. To turn a New York time (UTC-5) into Singapore time (UTC+8), the difference is 8 - (-5) = 13 hours:
=A2+(8-(-5))/24A 09:00 New York call is 22:00 the same day in Singapore. Going the other way, from Singapore to New York, the difference is -13, so subtract 13/24.
Keep offsets in cells with absolute references#
Typing offsets into every formula invites mistakes. Put each offset in a cell and point your formula at it. If E2 holds 8, then in B2:
=A2+$E$2/24Fill it down (Windows: Ctrl+D; Mac: ⌘D) and every row uses the same offset, because $E$2 is an absolute reference that doesn't move. A2 has no $, so it changes to A3, A4 and so on. The guide to the dollar sign in formulas explains why, and you can practise absolute references step by step.
For several cities, build a small offset table, with city names in E2:E6 and offsets in F2:F6. Then look up both ends of the conversion (XLOOKUP needs Excel 2021 or Microsoft 365):
=A2+(XLOOKUP(C2,$E$2:$E$6,$F$2:$F$6)-XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6))/24Here B2 is the source city and C2 the target city. When daylight saving starts, you update one cell in the table instead of every formula.
Times without dates: wrap in MOD#
If your cells hold only a time, such as 22:00 with no date, adding hours can push the value past 1 (more than a full day). It may still display as 03:30, but sorting and comparisons go wrong. Subtracting can go below 0, and Excel shows a negative time as ######. Wrap the result in MOD to keep just the time part:
=MOD(A2+5.5/24,1)For 22:00, this returns 03:30. MOD(...,1) keeps the remainder after whole days, so the result always falls between 00:00 and 23:59. The catch: you lose the information that it's the next day. When the day matters, store full date-times. To split a date-time back into its parts later, see how to remove the time from a date.
The daylight saving trap#
Excel has no time zone database, so it can't know about daylight saving time (DST, when clocks move forward an hour for summer). Offsets change during the year, and countries don't change on the same date.
A real example: in 2026, clocks in the United States move forward on 8 March, but the United Kingdom waits until 29 March. On 10 March 2026, 14:00 in London is 10:00 in New York, a 4-hour gap instead of the usual 5. A formula with a fixed -5 would be an hour wrong for those three weeks.
To stay accurate:
- Put the offset next to each row, or in a dated offset table, rather than one fixed number.
- Check the offset for the actual date of each event, not today's offset.
- Store system data in UTC and convert only for display. UTC never changes for daylight saving.
Convert text timestamps first#
Times copied from a system or a website are often text, like 2026-01-15T20:00:00Z. Adding hours to text gives #VALUE!. Turn it into a real date-time first:
=DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,12,8))LEFT takes the date part, MID takes the time part, and the two functions turn each into a number. Then add your offset as usual. To check how far apart two converted times are, see how to calculate a time difference.
Practise it#
Converting time zones is date arithmetic plus one locked cell. Practise date basics in a live spreadsheet: you'll add days and work with date serial numbers, and each answer is checked as you type. Then practise absolute references so your offset cell stays put when you fill a formula down. Try the exercise on this page to convert a UTC meeting time to Singapore.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
A2 holds a meeting at 15 Jan 2026, 20:00 UTC. Singapore is 8 hours ahead (in E2). In B2, add the offset to A2 as a fraction of a day, lock E2 with $, and show the result with TEXT in the format "d mmm hh:mm".
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Singapore time | |||||
| 2 | ||||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 |
Tip: Hours become days when you divide by 24: A2+$E$2/24. Then wrap it in TEXT(..., "d mmm hh:mm").
Practise this in a lesson
Frequently asked questions
Does Excel have a time zone conversion function?
No. Excel's worksheet functions have no time zone database. You add or subtract the hour difference yourself, for example =A2+8/24 to go from UTC to Singapore time.
How do I convert UTC to my local time in Excel?
Add your UTC offset divided by 24. For New York in winter (UTC-5), use =A2-5/24. For India (UTC+5:30), use =A2+5.5/24.
How do I convert time zones with half-hour offsets?
Use a decimal number of hours: 5.5 for 5 hours 30 minutes, or 5.75 for 5 hours 45 minutes. Then divide by 24 as usual, or use TIME(5,30,0).
Why is my converted time off by one hour?
Usually daylight saving time. Many countries change their offset for part of the year, and they don't all change on the same date. Look up the offset for the date in your data and update it.
How do I get the current UTC time in Excel?
There is no worksheet function for it. NOW() returns your computer's local time, so subtract your own UTC offset: =NOW()-1/24 if you are one hour ahead of UTC.