Fix CSV Imports Changing All-Day Event End Dates in Spreadsheets
Question details
The user needs to properly import a calendar CSV file into a spreadsheet so that the end dates of one-day all-day events are not incorrectly pushed to the next day.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Importing a CSV file containing calendar events into spreadsheet software to manage or edit schedules.
- Observed behavior
- When the CSV file is opened, one-day all-day events display an incorrect end date that falls on the following day due to date interpretation errors.
Before importing, open the CSV file in a basic text editor like Notepad to verify the original date format and ensure the dates are actually correct before the spreadsheet software applies its automatic formatting.
Use Get Data / Power Query to Import the CSV
Importing the CSV through the data query tool prevents automatic formatting errors by allowing you to explicitly set the column as a Date type before loading.
Spreadsheet software often attempts to guess the format of incoming CSV data. If your system's regional settings differ from the CSV date structure, it may add 24 hours to an all-day event or interpret the month and day backwards. Using the data import wizard gives you full control over the parsing process.
Navigate to the Data tab on your ribbon, select 'Get Data', then choose 'From File', and click on 'From CSV'.
Locate the problematic calendar CSV file on your computer and click 'Import' to open the preview window.
In the data preview window, highlight the columns containing the start and end dates. Change their data type to 'Date' and ensure the correct locale or format (such as YMD or MDY) is selected.
Click 'Load' or 'Close & Load' to bring the properly formatted dates into your spreadsheet.

Fix Existing Columns with Text to Columns
If the CSV is already open and the end dates are displaying incorrectly, you can use the Text to Columns feature to re-parse the existing data.
Easily Import and Fix CSV Event Dates with WPS Spreadsheet
WPS Spreadsheet provides robust data import and text-to-column wizards that perfectly interpret CSV dates, ensuring your calendar events retain their accurate start and end times without frustrating timezone or format shifts.
- 1. Open WPS Spreadsheet: Launch WPS Office, open the Spreadsheet module, and navigate to the 'Data' tab.
- 2. Import the CSV file: Click 'Import Data', select your CSV file, and follow the built-in wizard to safely load your calendar events.
- 3. Set the column format: During the import steps, explicitly set the event end-date columns to 'Date' to prevent automatic format shifting.

Frequently Asked Questions
Why do my all-day events shift to the next day when imported?
Spreadsheet software sometimes misinterprets plain text dates based on system locale settings. Furthermore, timezone offsets or automatic time rounding—such as defaulting a blank time to midnight—can inadvertently push an all-day event's end time to the following calendar day.
Can I change the default date format my spreadsheet uses for CSV files?
The default date interpretation is usually dictated by your operating system's regional settings rather than the spreadsheet software itself. To change how files open by default, you would need to adjust your OS region and date format settings. Alternatively, you can always use the 'Get Data' feature to override defaults on a per-file basis.
How do I remove unwanted time data attached to my CSV dates?
You can select the date column, right-click, and choose 'Format Cells' to apply a standard Date format that hides the time. If you need to entirely remove the time data rather than just hide it, use the 'Text to Columns' feature with a space delimiter to split the date and the time into two separate columns.




