Unable To Convert Worksheets Data To A Flatfiles: Unable to Convert Worksheets Data To a FlatFiles Excel help response (08 Nov 2022)

Symptoms. After converting CSV to Excel, dates are formatted incorrectly, days and months are swapped, some dates are changed to text, and some text values are auto formatted as dates.
Cause. In your csv file, dates are written in the format different from the default date format set in your operating system, because of which Excel fails to interpret the dates correctly.
Solution. Depending on exactly what problem you are faced with, try out one of the following solutions.

Days and months are mixed up

When the date formats in the Windows Regional settings and csv file are different, there is no way for Excel to determine that mm/dd/yy dates it is looking for are stored in the dd/mm/yy format in that particular file. As a result, the day and month units are reversed: Jan-3 becomes Mar-1, Jan-10 becomes Oct-1, and so on. Moreover, dates after Jan-12 are converted to text strings because there exist no 13th, 14th, etc. months.
For the dates to be imported correctly, run Text Import Wizard, and pick the appropriated Date format in step 3:

Some values are converted to dates

Microsoft Excel is designed to make it easier to enter various kinds of values. Therefore, if Excel believes that a given value represents a date, it is auto formatted as a date. For example, the text string apr23 looks very much like April 23, and 11/3 resembles November 3, so both values are converted to dates.
To stop Excel from changing text values to dates, use the already familiar approach: convert CSV to Excel by importing it. In step 3 of the Text Import Wizard, select the problematic column and change its format to Text.

Dates are formatted incorrectly

When a csv file is opened in Excel, the dates are normally displayed in the default format. For example, in your original file, you may have 7-May-21 or 05/07/21, while in Excel it appears as 5/7/2021.
To display dates in the desired format, make use of the Format Cells feature:

Select the column of dates.
Press Ctrl + 1 to open the Format Cells dialog box.
On the Number tab, choose Date under Category.
Under Type, pick the desired formatting.
Click OK.

Regards,
Rachel Gomez

	Symptoms. After converting CSV to Excel, dates are formatted incorrectly, days and months are swapped, some dates are changed to text, and some text values are auto formatted as dates. Cause. In your csv file, dates are written in the format different from the default date format set in your operating system, because of which Excel fails to interpret the dates correctly. Solution. Depending on exactly what problem you are faced with, try out one of the following solutions. Days and months are mixed up When the date formats in the Windows Regional settings and csv file are different, there is no way for Excel to determine that mm/dd/yy dates it is looking for are stored in the dd/mm/yy format in that particular file. As a result, the day and month units are reversed: Jan-3 becomes Mar-1, Jan-10 becomes Oct-1, and so on. Moreover, dates after Jan-12 are converted to text strings because there exist no 13th, 14th, etc. months. For the dates to be imported correctly, run Text Import Wizard, and pick the appropriated Date format in step 3: Some values are converted to dates Microsoft Excel is designed to make it easier to enter various kinds of values. Therefore, if Excel believes that a given value represents a date, it is auto formatted as a date. For example, the text string apr23 looks very much like April 23, and 11/3 resembles November 3, so both values are converted to dates. To stop Excel from changing text values to dates, use the already familiar approach: convert CSV to Excel by importing it. In step 3 of the Text Import Wizard, select the problematic column and change its format to Text. Dates are formatted incorrectly When a csv file is opened in Excel, the dates are normally displayed in the default format. For example, in your original file, you may have 7-May-21 or 05/07/21, while in Excel it appears as 5/7/2021. To display dates in the desired format, make use of the Format Cells feature: Select the column of dates. Press Ctrl + 1 to open the Format Cells dialog box. On the Number tab, choose Date under Category. Under Type, pick the desired formatting. Click OK. Regards, Rachel Gomez
	rachel
	Posted by RachelGomez161999 on 08 Nov 2022

Unable to Convert Worksheets Data To a FlatFiles

Excel Help for Unable To Convert Worksheets Data To A Flatfiles in Analyzing Corporate Financial Data