How to convert MT5 server time to local time in Excel

A step-by-step method with plain formulas: real dates from text, UTC, local time and the daylight saving problem.

Trade history exported from MetaTrader 5 carries the broker’s server time, which rarely matches the clock on your wall. If you want to know what hour of your own day you tend to trade badly, or to line a trade up with a news release listed in your own time zone, you need to convert the timestamps. This page shows how to do it in a spreadsheet, including the awkward part, the change of the server offset when daylight saving starts or ends. It uses plain formulas and no add-ins.

Know your server offset first

Every broker chooses its own server time. Many use a fixed offset from UTC that changes with daylight saving in the United States or Europe, but this is a convention of each company, not a rule of the platform. The background is in MT5 server time explained. To find your broker’s offset, compare the time of the latest quote in Market Watch with a UTC clock, or read the broker’s own trading hours page. Write down the offset in hours for winter and for summer; you will need both.

Step one: turn text into a real date

The exported time often arrives as text in a pattern like year, month and day separated by dots, followed by the time. Excel does not always treat that as a date, which is one of the problems listed in MT5 history export problems in Excel. A formula that works regardless of your regional settings builds the date from its pieces. If the text is in cell A2, use this idea: take the first four characters as the year, characters six and seven as the month, characters nine and ten as the day, and add the time taken from the characters that follow. In a formula that reads DATE of LEFT, MID and MID, plus TIMEVALUE of the time text. Check one row by eye before you fill the column down.

Step two: shift to UTC

With a true date and time in a cell, subtract the server offset divided by 24. Excel stores a day as 1, so one hour is one twenty-fourth. If the server is three hours ahead of UTC, UTC time equals server time minus 3 divided by 24. Put the offset in its own cell, for example B1, so you can change it without editing every formula. UTC is the best middle format because every other conversion starts from it.

Step three: shift to your local time

Add your own offset from UTC divided by 24 to the UTC value. A trader on a fixed offset of plus five hours, with no daylight saving, adds 5 divided by 24. A trader whose own country changes its clock needs the same treatment as the broker. If you want local time directly, the combined shift is your offset minus the server offset, divided by 24. Keep a column for UTC anyway, since it lets you compare results across brokers.

The daylight saving problem

If the server offset changes during the year, a single fixed number is wrong for part of your data. The simplest fix is an offset table on a separate sheet with three columns: start date, end date and offset. Then look up each trade’s date in that table. An alternative with fewer moving parts is to split the data into two blocks, one for the months before the change and one after, and apply a different offset to each. The reasons dates drift around the change, and what the journal does about them, are described in daylight saving and your journal dates. Trades closed in the hours around the change are the ones to check by hand.

What to do with the converted times

• Group results by local hour to see when your trading is strongest or weakest, remembering that few trades per hour make weak evidence.
• Mark news times in the same zone and look at how you behaved around them; the routines in how to journal CPI days and NFP days use this kind of review.
• Compare the hour of day with the session times for your region, such as forex session times in India.
• Check which date a trade belongs to, using the ideas in open time versus close time.

A worked example (hypothetical)

Imagine an exported close time of 2026.03.12 22:30:00 from a server that is two hours ahead of UTC at that date. Subtracting two hours gives 20:30 UTC. A trader whose own offset is plus five hours then sees 01:30 on the next calendar day. Without the conversion, the trade would have been counted as a late-evening trade on the 12th; with it, the trade falls on the 13th in the trader’s own day. The values are invented, but they show how the date itself can change, not only the hour.

What the journal app does

AI Trading Journal, a Windows app for MetaTrader 5 only, dates each entry by the last exit on the broker’s server clock and does not convert to your local time, so the dates match your broker statement. It is read-only, runs on your own PC and can create an Excel report, where you can apply your own conversion in a copy if you want hour-of-day studies in local time.

Limits and cautions

• Confirm your broker’s offset on its own pages; do not assume it from another broker.
• Formulas depend on how your spreadsheet reads text, so test on a few rows.
• Keep the original export untouched and work on a copy.
• The example values are invented.

Frequently asked questions

Related guides

• A local trading journal for MT5
• P&L calendar for MT5
• Trade notes, ratings and screenshots
• Trading progress and goals
• Back up your journal and move it to a new PC
• An offline trading journal for Windows
• How to set monthly trading goals
• Import your whole MT5 history
• How to tag trades
• Trade screenshots for your journal
• All trading journal comparisons