How-to
Fix common date and time problems
Each page takes one problem people run into with dates, timestamps and time zones, explains why it happens, and fixes it with a worked example you can open in the converter. Most also show the fix in Excel, Google Sheets or code.
For the background on a format or a concept — what ISO 8601 allows, how Unix time works, why daylight saving breaks software — read the guides.
New to UTC, time zones or Unix timestamps? Start with the basics.
Spreadsheets and CSV files
Excel and Sheets rewriting dates, day/month swaps, serial numbers and import formats.
-
How to Stop Excel Changing Dates in a CSV File
Excel rewrites dates, and codes that look like dates, when it opens or saves a CSV. Import the column as text, write dates unambiguously, repair the damage.
-
How to Fix Dates Where the Day and Month Are Swapped
03/04/2024 is 3 April in most of the world and March 4 in the US. Find which order a column uses, convert it to an unambiguous format, avoid silent swaps.
-
How to Turn a CSV Column of Timestamps Into Local Times
Unix or ISO 8601 timestamps from an export, turned into readable times in your own zone, each row with its own daylight-saving offset. No upload, no script.
-
How to Convert Excel Serial Numbers to Dates
45472 is 29 June 2024 to Excel. Convert serial numbers from exports, APIs and libraries into real dates, and know the 1900 leap-year bug and the 1904 system.
-
How to Fix Excel Dates That Are Four Years and One Day Off
Dates copied between workbooks moved by 4 years and 1 day? That is the 1900 vs 1904 date system. Find which one each workbook uses and shift the dates back.
-
How to Get Excel to Read ISO 8601 Timestamps With T and Z
Excel does not recognise 2024-06-29T13:45:07Z as a date. Convert ISO 8601 values with a Z or an offset to a form Excel reads, in UTC or in your own zone.
-
How to Convert Dates to the Exact Format an Import Requires
An import rejects your dates or reads them wrongly? Convert the column to the exact pattern it asks for, such as DD/MM/YYYY, with no time-zone label added.
Timestamps in code, logs and databases
Unix units, log lines and database values turned into readable dates in your zone.
-
How to Tell Unix Seconds From Milliseconds
A Unix timestamp can count seconds, milliseconds, microseconds or nanoseconds. Read the unit from the digits, convert it, and fix it in Excel or code.
-
How to Convert Log Timestamps to Your Local Time
Server logs in UTC and your clock somewhere else? Read Apache, nginx and ISO 8601 log times and convert them to your zone, daylight saving included.
-
How to Read PostgreSQL and MySQL Timestamps in Your Time Zone
Turn PostgreSQL timestamptz, MySQL DATETIME and SQL Server values into your local time, and know which carry a time zone and which only a wall clock.
Time zones and daylight saving
Clock changes and zone abbreviations that leave a time one hour off.
-
How to Fix Times That Are One Hour Off After Daylight Saving
Meetings or conversions an hour off around a clock change? See why fixed offsets fail, the weeks Europe and the US disagree, and how to convert by date.
-
EST or EDT? How to Write a Time Zone and Avoid an Hour Off
In North America EST is five hours behind UTC all year and EDT four. Write the right one or a city, and decode CST, IST and other shared abbreviations.
A problem that is not here?
The converter reads 82 date and time formats: paste a value and it says which format it read and in which zone.
Open the converter