Dates Automatically Converting from US for 1-12th Month

%3CLINGO-SUB%20id%3D%22lingo-sub-2654482%22%20slang%3D%22en-US%22%3EDates%20Automatically%20Converting%20from%20US%20for%201-12th%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2654482%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20be%20so%20grateful%20for%20help%20with%20this.%20When%20I%20open%20a%20sheet%20on%20my%20standard%2Finternational%20PC%20(created%20in%20mac%20on%20a%20USA%20date%20system)%2C%20some%20of%20these%20US%20style%20dates%20(mm%2Fdd%2Fyy)%20automatically%20convert%20to%20standard%20format%20(dd%2Fmm%2Fyy).%20Unfortunately%2C%20this%20leaves%20me%20with%20incorrect%20dates%2C%20as%20the%20mm%2Fdd%20is%20flipped%20for%20those%20days%20from%201st%20to%2012th%20month.%20How%20can%20I%20prevent%20this%3F%20I%20just%20want%20all%20the%20dates%20in%20one%20format%2C%20ideally%20my%20standard%20(but%20if%20I%20have%20to%20accept%20the%20US%20version%2C%20ok%2C%20as%20long%20as%20it%20is%20the%20correct%20date).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%2012th%20July%202021%20is%20initially%20written%20as%2007%2F12%2F21%20in%20US%20format%2C%20but%20converts%20automatically%20in%20my%20excel%20list%20to%207th%20December%202021%2C%20however%20in%20the%20same%20list%2C%20the%20date%20for%2013th%20July%202021%20remains%20as%2013th%20July%202021.%20This%20means%20changing%20the%20locale%20in%20power%20query%20changes%20the%20meaning%20of%20the%20entry%20for%20anything%201-12th%20of%20the%20month.%20Please%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2654482%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2656370%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20Automatically%20Converting%20from%20US%20for%201-12th%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2656370%22%20slang%3D%22en-US%22%3EExcel%20does%20not%20change%20dates%20all%20by%20itself%20if%20they%20are%20entries%20in%20cells.%20Apparently%20something%20else%20is%20at%20play%20here.%20You%20mention%20PowerQuery%3B%20is%20the%20query%20set%20to%20refresh%20when%20the%20file%20opens%3F%20You%20can%20tell%20PQ%20which%20locale%20to%20use%20for%20the%20query%20as%20a%20whole%2C%20but%20also%20when%20converting%20an%20individual%20column%20to%20date%20format.%20See%3A%20%3CA%20href%3D%22https%3A%2F%2Fwww.myonlinetraininghub.com%2Fchange-type-using-locale-with-power-query%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.myonlinetraininghub.com%2Fchange-type-using-locale-with-power-query%3C%2FA%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello! 

I'd be so grateful for help with this. When I open a sheet on my standard/international PC (created in mac on a USA date system), some of these US style dates (mm/dd/yy) automatically convert to standard format (dd/mm/yy). Unfortunately, this leaves me with incorrect dates, as the mm/dd is flipped for those days from 1st to 12th month. How can I prevent this? I just want all the dates in one format, ideally my standard (but if I have to accept the US version, ok, as long as it is the correct date). 

 

For example, 12th July 2021 is initially written as 07/12/21 in US format, but converts automatically in my excel list to 7th December 2021, however in the same list, the date for 13th July 2021 remains as 13th July 2021. This means changing the locale in power query changes the meaning of the entry for anything 1-12th of the month. Please help!

 

1 Reply
Excel does not change dates all by itself if they are entries in cells. Apparently something else is at play here. You mention PowerQuery; is the query set to refresh when the file opens? You can tell PQ which locale to use for the query as a whole, but also when converting an individual column to date format. See: https://www.myonlinetraininghub.com/change-type-using-locale-with-power-query