Select Custom from the number Category list.

If necessary, select the Number tab (it should appear by default).

Select Format Cells from the shortcut menu.

This selects the entire column and opens a shortcut menu as shown below.

RIGHT-CLICK the tile above the Start Date column header.

To reformat the date fields in an Excel spreadsheet Basic steps: select the date column, right-click and select Format Cells, then select Custom and enter yyyy-mm-dd in the text field. This is bad, because now your CSV import won't work.ĭON'T PANIC! Use the instructions below to change the date format in your spreadsheet. And after you make changes and save the CSV file, the wrong date format showing in Excel will be applied to the fields. What this means is that even if your CSV file is correct (open it in Notepad or Wordpad to see the raw data), the reformatted date field will be wrong in Excel. The PROBLEM is that Excel will automatically reformat what it recognizes as a date into the default date format configured for your system. Many users work with CSV files using a spreadsheet program like Microsoft® Excel. This is an international standard date format, designed to reduce confusion. The required format for date fields is yyyy-mm-dd.

Some of the CSV imports contain date fields, in particular the capture/schedules import. Just don't open the CSV file in Excel and expect the dates to be formatted correctly. Always use the *.xlsx version to make updates, in order to retain required date formatting. The CSV file will have the proper date format. When you're done and ready to import, Save As and select CSV (comma-separated-values) as the file type.

Use THE EXCEL-TYPE file to make future changes or updates. Make your changes, format all date columns as shown below, then save the file as an Excel spreadsheet. Summary of this page: Excel will respect your date formatting as long as you save the file as an XLSX (Excel file).

Opening a csv file in excel