How to change the default data reading method for dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1883150%22%20slang%3D%22en-US%22%3EHow%20to%20change%20the%20default%20data%20reading%20method%20for%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1883150%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%20I%20am%20encountering%20a%20problem%20when%20opening%20web%20downloaded%20data%20by%20my%20excel.%20I%20have%20downloaded%20a%20csv%20file%20from%20the%20web%2C%20with%20a%20column%20of%20strings%20as%20%22dd-mm-yyyy%22.%20My%20PC%20directly%20reads%20them%20as%20a%20date%20in%20the%20format%20of%20%22dd-mm-yyyy%22%20and%20displays%20them%20as%20%22dd%2Fmm%2Fyyyy%22%2C%20which%20is%20correct.%20However%2C%20when%20I%20open%20the%20same%20file%20from%20my%20laptop%2C%20it%20reads%20the%20strings%20as%20%22mm-dd-yyyy%22%2C%20and%20only%20convert%20the%20cells%20with%20less%20than%2012%20dates%20value%20faultly%20into%20%22mm%2Fdd%2Fyyyy%22%20while%20remains%20the%20others%20as%20strings.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20noticed%20that%20under%20the%20number%20ribbon%2C%20my%20PC%20default%20date%20location%20is%20English%20(Hong%20Kong)%2C%20while%20my%20laptop%20is%20English%20(United%20States).%20I%20think%20that%20might%20be%20the%20reason%20for%20that.%20Does%20anyone%20knows%20how%20to%20change%20the%20default%20setting%20of%20the%20date%20reading%20method%20or%20write%20a%20script%20for%20the%20checking%20in%20Macros%20VBA%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEg.%20The%20original%20csv%3A%2027-02-2020%26nbsp%3B%20%26nbsp%3B%2012-02-2020%3C%2FP%3E%3CP%3EMy%20PC%20shows%20as%3A%2027%2F2%2F2020%20(%22d%2Fm%2Fyyyy%22)%2012%2F2%2F2020%20(%22d%2Fm%2Fyyyy%22)%20True%3C%2FP%3E%3CP%3EMy%20Laptop%20shows%20as%3A%2027-02-2020%20(%22General%22)%2012%2F2%2F2020%20(%22m%2Fd%2Fyyyy%22)%20False%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1883150%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1883380%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20change%20the%20default%20data%20reading%20method%20for%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1883380%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20just%20adjusted%20the%20programs%20to%20be%20the%20same%20region%20default%2C%20and%20I%20finally%20figured%20out%20the%20problem%3A%3C%2FP%3E%3CP%3EWhen%20I%20directly%20click%20the%20link%20from%20Chrome%20or%20MS%20Edge%2C%20the%20file%20downloaded%20is%20good%2C%20but%20when%20I%20downloaded%20it%20by%20the%20VBA%20code%20Workbooks.Open(%22link%22)%2C%20it%20failed%20and%26nbsp%3B%20the%20date%20format%20started%20to%20confuse%20me.%20Anyone%20knows%20how%20to%20control%20the%20date%20format%20reading%20default%20of%20the%20Workbooks.Open%20function%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello everyone, I am encountering a problem when opening web downloaded data by my excel. I have downloaded a csv file from the web, with a column of strings as "dd-mm-yyyy". My PC directly reads them as a date in the format of "dd-mm-yyyy" and displays them as "dd/mm/yyyy", which is correct. However, when I open the same file from my laptop, it reads the strings as "mm-dd-yyyy", and only convert the cells with less than 12 dates value faultly into "mm/dd/yyyy" while remains the others as strings.

 

I have noticed that under the number ribbon, my PC default date location is English (Hong Kong), while my laptop is English (United States). I think that might be the reason for that. Does anyone knows how to change the default setting of the date reading method or write a script for the checking in Macros VBA please?

 

Eg. The original csv: 27-02-2020    12-02-2020

My PC shows as: 27/2/2020 ("d/m/yyyy") 12/2/2020 ("d/m/yyyy") True

My Laptop shows as: 27-02-2020 ("General") 12/2/2020 ("m/d/yyyy") False

 

 

1 Reply

I have just adjusted the programs to be the same region default, and I finally figured out the problem:

When I directly click the link from Chrome or MS Edge, the file downloaded is good, but when I downloaded it by the VBA code Workbooks.Open("link"), it failed and  the date format started to confuse me. Anyone knows how to control the date format reading default of the Workbooks.Open function?