How do I rectify the date format when viewing export data in Excel?

Dates from responses are exported from the application in a MM/DD/YYYY format. Depending on the Region set in Windows, Excel can apply a different date format to the dates and cause inconsistency in date responses.

You can unify all date responses in Excel to follow the MM/DD/YYYY format by importing your exported data as raw text. If you want to filter by date, convert your raw text data to the date format using a equation and then specify the date format.

Note: The workflow can differ depending on which version of Excel is used.

Example

The date January 5th, 2002 is outputted from the application as 01/05/2002 (MM/DD/YYYY). If your Region is set to the United Kingdom, Excel interprets 01/05/2002 (DD/MM/YYYY) as May 1st, 2002.

Or the date December 14th, 2018 is outputted from the application as 12/14/2018 (MM/DD/YYYY). If your Region is set to the United Kingdom, Excel can not interpret the date as DD/MM/YYYY because there is not a 14th month. Excel will leave 12/14/2018 as text in the MM/DD/YYYY format.

Detailed Steps

1. Export dates as raw text

To ensure all date responses are consistent, export dates as raw text data.

1. Open a blank workbook in Excel.
2. Click Data > From Text/CSV.
3. From your computer, select the CSV you exported from the application.

Result: A dialog opens with a preview of the data.

4. Click Edit.
5. Right-click the column with the dates and select Change Type > Text.
6. In Change Column Type dialog, click Replace current.
7. Close the preview dialog and in the Power Query Editor dialog, click Keep.

Result: Excel will import your data with the selected settings.

2. Convert using a equation

Follow the steps below to convert the raw text date into a format Excel can filter.

1. Copy one of the equations below.

Note: Select the equation based on the language you are using Excel in.

2. In the Date column, click into a date cell and paste the equation to the right of the date.

3. Press Enter.

Result: The new date format is automatically reflected in the cell.

4. Optional - Repeat steps 1-3 for additional dates.

Note: You do not need to repeat steps 1-3 if your version of Excel automatically generates an answer for every date.

Equation

English Equation

=IF(LEN(INDIRECT(ADDRESS(ROW(),COLUMN()-1)))=0,"",IF(FIND(" ",INDIRECT(ADDRESS(ROW(),COLUMN()-1)),1)=9,DATE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),5,4),LEFT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),1),MID(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),3,1)),IF(FIND(" ",INDIRECT(ADDRESS(ROW(),COLUMN()-1)),1)=11,DATE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),7,4),LEFT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),2),MID(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),4,2)),IF(AND(FIND(" ",INDIRECT(ADDRESS(ROW(),COLUMN()-1)),1)=10,FIND("/",INDIRECT(ADDRESS(ROW(),COLUMN()-1)),1)=3),DATE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),6,4),LEFT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),2),MID(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),4,1)),IF(AND(FIND(" ",INDIRECT(ADDRESS(ROW(),COLUMN()-1)),1)=10,FIND("/",INDIRECT(ADDRESS(ROW(),COLUMN()-1)),1)=2),DATE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),6,4),LEFT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),1),MID(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),3,2)),"ERROR")))))

French Equation

=SI(NBCAR(INDIRECT(ADRESSE(LIGNE();COLONNE()-1)))=0;"";SI(TROUVE(" ";INDIRECT(ADRESSE(LIGNE();COLONNE()-1));1)=9;DATE(STXT(INDIRECT(ADRESSE(LIGNE();COLONNE()-1));5;4);GAUCHE(INDIRECT(ADRESSE(LIGNE();COLONNE()-1));1);STXT(INDIRECT(ADRESSE(LIGNE();COLONNE()-1));3;1));SI(TROUVE(" ";INDIRECT(ADRESSE(LIGNE();COLONNE()-1));1)=11;DATE(STXT(INDIRECT(ADRESSE(LIGNE();COLONNE()-1));7;4);GAUCHE(INDIRECT(ADRESSE(LIGNE();COLONNE()-1));2);STXT(INDIRECT(ADRESSE(LIGNE();COLONNE()-1));4;2));SI(ET(TROUVE(" ";INDIRECT(ADRESSE(LIGNE();COLONNE()-1));1)=10;TROUVE("/";INDIRECT(ADRESSE(LIGNE();COLONNE()-1));1)=3);DATE(STXT(INDIRECT(ADRESSE(LIGNE();COLONNE()-1));6;4);GAUCHE(INDIRECT(ADRESSE(LIGNE();COLONNE()-1));2);STXT(INDIRECT(ADRESSE(LIGNE();COLONNE()-1));4;1));SI(ET(TROUVE(" ";INDIRECT(ADRESSE(LIGNE();COLONNE()-1));1)=10;TROUVE("/";INDIRECT(ADRESSE(LIGNE();COLONNE()-1));1)=2);DATE(STXT(INDIRECT(ADRESSE(LIGNE();COLONNE()-1));6;4);GAUCHE(INDIRECT(ADRESSE(LIGNE();COLONNE()-1));1);STXT(INDIRECT(ADRESSE(LIGNE();COLONNE()-1));3;2));"ERROR")))))

German Equation

=WENN(LÄNGE(INDIREKT(ADRESSE(ZEILE();SPALTE()-1)))=0;"";WENN(FINDEN(" ";INDIREKT(ADRESSE(ZEILE();SPALTE()-1));1)=9;DATUM(TEIL(INDIREKT(ADRESSE(ZEILE();SPALTE()-1));5;4);LINKS(INDIREKT(ADRESSE(ZEILE();SPALTE()-1));1);TEIL(INDIREKT(ADRESSE(ZEILE();SPALTE()-1));3;1));WENN(FINDEN(" ";INDIREKT(ADRESSE(ZEILE();SPALTE()-1));1)=11;DATUM(TEIL(INDIREKT(ADRESSE(ZEILE();SPALTE()-1));7;4);LINKS(INDIREKT(ADRESSE(ZEILE();SPALTE()-1));2);TEIL(INDIREKT(ADRESSE(ZEILE();SPALTE()-1));4;2));WENN(UND(FINDEN(" ";INDIREKT(ADRESSE(ZEILE();SPALTE()-1));1)=10;FINDEN("/";INDIREKT(ADRESSE(ZEILE();SPALTE()-1));1)=3);DATUM(TEIL(INDIREKT(ADRESSE(ZEILE();SPALTE()-1));6;4);LINKS(INDIREKT(ADRESSE(ZEILE();SPALTE()-1));2);TEIL(INDIREKT(ADRESSE(ZEILE();SPALTE()-1));4;1));WENN(UND(FINDEN(" ";INDIREKT(ADRESSE(ZEILE();SPALTE()-1));1)=10;FINDEN("/";INDIREKT(ADRESSE(ZEILE();SPALTE()-1));1)=2);DATUM(TEIL(INDIREKT(ADRESSE(ZEILE();SPALTE()-1));6;4);LINKS(INDIREKT(ADRESSE(ZEILE();SPALTE()-1));1);TEIL(INDIREKT(ADRESSE(ZEILE();SPALTE()-1));3;2));"ERROR")))))

3. Specify a custom date format

Specify a custom date format for all date responses to use.

1. Select the date column.
2. Under Home > Number, select Number Format > More Number Formats.
3. Under the Category section, click Custom.
4. In the Type field, enter a custom time format.

Note:

• In the example, D/M/YYYY H:MM AM/PM is Day/Month/Year Hours:Minutes in 12-hour clock time format. 
• The custom format entered in the Type field will be automatically reflected in the Sample section.

5. Click OK.

Result: The custom time format is applied to all dates.

More Information

Read this Knowledge Base article to learn more about How do I rectify date and time formatting issue when exporting data into Excel?