Opening CSV In EXCEL date data is wrong

[Keegan4123]

Is there any way to change the default settings for how a file is opened in excel when in CSV in regards to dates?

I have a csv from a program that exports out okay, but when I try to open it all the dates either do not even come in as dates, or if they do they have month/day messed up.

I have tried the text to columns, but it doesn't fix both, will usually only fix the non dates, but the wrong dates remain wrong.

The odd thing is if I open the exact file in google sheets, everything works as it should. Really don't want to have to add that step as this will be a monthly thing

Here is the txt of the data:
Vehicle,Type,Work Order,Completion Date,Labor Subtotal,Parts Subtotal
CV02,COMBO,#6240,05/25/2022 1:02 PM,$465.54,$264.57---Non Date
CV02,COMBO,#6446,05/10/2022 11:00 PM,$534.18,$589.07 ---Real Date but October 5th
CV02,COMBO,#6521,05/31/2022 11:00 PM,$91.11,$141.13---Non Date
CV03,COMBO,#6427,05/03/2022 11:00 PM,$0.00,$114.32---Real Date but March 5
CV03,COMBO,#6428,05/05/2022 11:00 PM,$0.00,$0.00---Real Date but May 5
CV03,COMBO,#6502,05/24/2022 11:00 PM,$0.00,$0.00---Non Date
CV03,COMBO,#6507,05/25/2022 11:00 PM,$121.48,$0.00---Non Date
CV04,COMBO,#6400,05/24/2022 2:40 PM,$485.92,"$1,248.95"---Non Date
CV04,COMBO,#6458,05/11/2022 11:00 PM,$60.74,$0.00---Real Date but Nov 5
CV04,COMBO,#6496,05/23/2022 11:00 PM,$410.21,$0.00---Non Date


I am at a bit of a loss. Have reached out the software vendor for help but they are struggling too.

 

[johnnyL]

This is what I get when I open that up into excel:

Keegan4123.csv
	A	B	C	D	E	F	G
1	Vehicle	Type	Work Order	Completion Date	Labor Subtotal	Parts Subtotal
2	CV02	COMBO	#6240	5/25/2022 13:02	$465.54	$264.57
3	CV02	COMBO	#6446	5/10/2022 23:00	$534.18	$589.07
4	CV02	COMBO	#6521	5/31/2022 23:00	$91.11	$141.13
5	CV03	COMBO	#6427	5/3/2022 23:00	$0.00	$114.32
6	CV03	COMBO	#6428	5/5/2022 23:00	$0.00	$0.00
7	CV03	COMBO	#6502	5/24/2022 23:00	$0.00	$0.00
8	CV03	COMBO	#6507	5/25/2022 23:00	$121.48	$0.00
9	CV04	COMBO	#6400	5/24/2022 14:40	$485.92	$1,248.95
10	CV04	COMBO	#6458	5/11/2022 23:00	$60.74	$0.00
11	CV04	COMBO	#6496	5/23/2022 23:00	$410.21	$0.00
12
Keegan4123

 

[alansidman]

I suspect that you are not in the US and the dates are in US format and your preferences are set for European Format. You may want to change your settings to US format to allow for proper alignment for this particular project.

 

[Alex Blakenburg]

Assuming your Region Date setting is dd/mm/yyyy and you don't want to change it then importing the file with some VBA code or Power Query is the easiest option.

Text to Columns will work but it is easier if you only want the date portion and don't need the time portion.

To use Text to Column, go to text to columns and select delimite of "space". This will give you 3 columns 1 for date and then time in 2 columns with the last having PM in it.

For the Date column set it to the incoming date format of MDY.
If you can live without the time then skip the next 2 columns.

If you need the time don't skip the other columns but you would need a formula to put the 2 columns back together in which case you may as well use a formula to do the lot.

 

[Keegan4123]

I suspect that you are not in the US and the dates are in US format and your preferences are set for European Format. You may want to change your settings to US format to allow for proper alignment for this particular project.

How would I change my preferences. I have looked and can't figure out which setting

 

[alansidman]

this should help. it goes from US to UK in the tutorial, but the process is the same to change it to the other direction.

How to Change Default Date Format from US to UK in Excel (3 Ways)

3 quick and easy ways to change the default date format from the US to the UK in Excel. Download our practice book, modify data and exercise.

www.exceldemy.com

 

[Alex Blakenburg]

I will be surprised if the options in the link above will work for you.

1. Changing the Windows Region settings
   I don't think you want to be chaning a global setting for a once a month file import
2. Setting a custom date setting to US Locale
   I don't believe this will give you a different result in terms of importing the US Date format than you are getting now.
3. Text to Columns
   This will only work if you follow my previous instructions, it won't work the way it is explained in the link because the Time portion seems to be throwing it off.

I am in Australia and have the default date format as dd/mm/yyyy and the below works for me:
(Copy and paste it into a standard VBA module)

Sub ImportCSVUSDateTime()

    Dim strFile As String

    strFile = Application.GetOpenFilename(FileFilter:="csv files (*.csv*), *.csv*", Title:="Choose a CSV file to open", MultiSelect:=False)
    
    If strFile = False Then Exit Sub
    
    Workbooks.Open Filename:=strFile, local:=False

End Sub