Fix CSV Dates Shifting by One Day in Excel Power Query
Question details
The user needs dates imported from a CSV file to retain the intended calendar date instead of appearing one day earlier in Excel.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Importing CSV timestamps that include UTC offsets, such as 2020-02-04 00:10:50+00:00, and loading the results into an Excel worksheet.
- Observed behavior
- A source timestamp dated February 4 appears as February 3 after import. Converting 00:10:50 UTC to UTC-8 produces 16:10:50 on the previous day.
Open the CSV in a text editor and check whether the affected timestamps contain Z or an offset such as +00:00. Decide whether the result should preserve the date written in the source, represent the UTC date, or reflect a specific reporting time zone.
Preserve the calendar date written in the CSV
Use this approach when the date must match the source text exactly, regardless of the timestamp's time-zone offset.
An automatic type conversion or a later time-zone conversion can change the calendar date. Inspect the query to identify the first step where the value changes rather than assuming every CSV import converts timestamps to local time.
In Excel, select Data > Queries & Connections, right-click the affected query, and select Edit. For a new import, select Data > From Text/CSV, choose the file, and click Transform Data.
In Query Settings > Applied Steps, click each step in order and compare an affected timestamp. Check Changed Type and any later steps involving date, datetime, or datetimezone conversion.
Remove or edit the Changed Type step that converts the affected column. At the step before conversion, select the timestamp column and choose Transform > Data Type > Text. If prompted, replace the existing conversion rather than adding a text conversion after the date has already shifted.
For source strings beginning with yyyy-MM-dd, select Add Column > Custom Column. Name it SourceDate and enter Date.FromText(Text.Start([Timestamp], 10), [Format="yyyy-MM-dd", Culture="en-US"]). Replace Timestamp with the actual column name, click OK, and set the new column's data type to Date.
Confirm that 2020-02-04 00:10:50+00:00 produces February 4, 2020. Select Home > Close & Load, then verify the same row in the worksheet.
Convert timestamps to the intended time zone before extracting dates
Use this approach when the date should represent UTC or a specific reporting offset rather than the source's written date.
Review CSV Dates with WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Excel format compatibility for easy migration. Use WPS Spreadsheets to review CSV data and corrected workbooks; it does not repair Excel Power Query steps or replace its time-zone transformation workflow.
Frequently Asked Questions
Why does February 4 become February 3 after importing my CSV?
A timestamp near midnight can cross into the previous day when converted to a negative UTC offset. For example, 2020-02-04 00:10:50+00:00 becomes February 3 at 16:10:50 in UTC-8. Open Data > Queries & Connections > Edit and inspect Applied Steps to find where the conversion occurs.
Is removing Changed Type enough to fix the dates?
Only if that step causes the unwanted conversion. In Power Query Editor, compare the preview before and after Changed Type, then inspect later steps too. Keep the original timestamp as text if you need to preserve its written date.
Can changing the Excel cell format restore the original date?
No. Home > Number Format changes how the stored value is displayed, not the underlying date. Correct the timestamp handling in Power Query and reload the query instead.
How do I keep the UTC date rather than my local date?
In Add Column > Custom Column, parse the original text with DateTimeZone.FromText, convert it with DateTimeZone.SwitchZone using an offset of 0, remove the zone with DateTimeZone.RemoveZone, and then extract the date with Date.From.
Should I add one day to every imported date?
No. Only timestamps that cross midnight during conversion will change calendar dates. Adding a day to every row can corrupt otherwise correct dates. Preserve the source date or explicitly convert to the intended time zone before extracting the date.
