Date formatting - mix of slash and dash dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1850611%22%20slang%3D%22en-US%22%3EDate%20formatting%20-%20mix%20of%20slash%20and%20dash%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1850611%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-11-03_14-31-24.png%22%20style%3D%22width%3A%20303px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F231168i49C15E770E6EC587%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222020-11-03_14-31-24.png%22%20alt%3D%222020-11-03_14-31-24.png%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3ESince%20changing%20my%20laptop%2C%20all%20excel%20exports%20from%20our%20OM-system%20looks%20horrible%20in%20regards%20to%20the%20date%20format.%20The%20big%20issue%20is%20that%20all%20regions%20enter%20their%20dates%20differently%20in%20our%20OM%20system.%20I%20have%20no%20idea%20what%20my%20old%20Excel%20installation%20was%20set%20up%20to%20make%20it%20work%2C%20I%20simply%20can't%20get%20my%20head%20around%20how%20to%26nbsp%3B%3CSPAN%3Eformat%20all%20dates%20at%20once%20without%20changing%20all%20slashes%20to%20dashes.%20Even%20if%20I%20would%20change%20the%20Slashes%20to%20Dashes%20I%20still%20have%20the%20problem%20of%20the%20order%20of%20the%20dates%20as%20%22slash%20and%20dash-dates%22%20has%20a%20different%20ordering.%20It%20would%20be%20nice%20if%20the%20formatting%20could%20be%20done%20in%20Excel%20and%20not%20by%20tempering%20with%20the%20date%20settings%20of%20my%20PC...%20An%20example%20file%20is%20attached.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1850611%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1873194%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20formatting%20-%20mix%20of%20slash%20and%20dash%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1873194%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F855942%22%20target%3D%22_blank%22%3E%40Averheim%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E(i'm%20using%20a%20non-English%20system%20.%20Guess%20what%20i%20mean%20if%20my%20translation%20guesses%20are%20totally%20wrong...)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ea%20formula%20solution%20might%20be%3A%3C%2FP%3E%3CP%3E%3DDATEVALUE(B2)%3C%2FP%3E%3CP%3EBut%20that%20only%20works%20if%20your%20system%20(windows)%20is%20set%20to%20US-dates%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAnother%20way%20that%20usually%26nbsp%3Bworks%3A%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESelect%20column%20B%3C%2FP%3E%3CP%3EData-%26gt%3BText%20to%20Columns%3C%2FP%3E%3CP%3E%5BNext%5D-%26gt%3B%5BNext%5D%3C%2FP%3E%3CP%3ENow%20you%20should%20be%20able%20to%20select%20DATE%20and%20the%20format%20MonthDayYear%20MDY%26nbsp%3B%20(in-data%20format.%20Not%20what%20you%20want).%3C%2FP%3E%3CP%3E%5BEnd%5D%3C%2FP%3E%3CP%3EIf%20you%20are%20lucky%20the%20Text-dates%20(%2F)%20are%20transformed%20to%20real%20dates%20and%20the%20%22real%20dates%20%22%20are%20untouched.%3C%2FP%3E%3CP%3EChang%20the%20display-format%20If%20necessary%20(Ctrl%2Bshift%2B3%26nbsp%3B%20Or%20right%20click.%20Format%20cells%2C%20Nubers%20format.%20Date)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EThe%20problem%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESome%20of%20your%20dates%20are%20%22real%22%20excel%20dates%20(-).%20And%20some%20are%20text%20(%2F)%3C%2FP%3E%3CP%3E%3CEM%3Eselect%20column%2C%20Right%20click%2C%20Format%20cells%2C%20alignment%2C%20Horizontal%3DGeneral%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3ENow%20you%20will%20se%20that%20the%20text%20is%20aligned%20to%20the%20Left%20and%20the%20%22real%22%20Dates%20to%20the%20right.%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EOr%20change%20display-format%20of%20the%20column%20t%20general%20(Ctrl%2B0).%20The%20text-dates%20will%20still%20be%20text%20but%20the%20real%2C%20calculable%26nbsp%3Bdates%20will%20show%20upp%20as%20days%20since%201900-01-01%26nbsp%3B%20(44161)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20a%20formula%20that%20checks%20if%20its%20a%20date%20(number)%20or%20a%20text%20and%20only%20fix%20the%20text-Dates%3C%2FP%3E%3CP%3E%3DIF(ISNUMBER(B2)%2CB2%2CDATE(MID(B2%2C7%2C4)%2CMID(B2%2C1%2C2)%2CMID(B2%2C4%2C2)))%3C%2FP%3E%3CP%3EBut%20i%20think%20that%20should%20give%20the%20same%20result%20as%20method%201%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eelse.%20Use%20power%20query%20with%20%22Locale%22%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Finternationalization-power-query-d42b9390-1fff-413f-8120-d7df0ced20b9%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Finternationalization-power-query-d42b9390-1fff-413f-8120-...%3C%2FA%3E%3C%2FP%3E%3CP%3EExpand%3A%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3EUse%20a%20non-default%20locale%20setting%20on%20a%20Change%20Type%20operation%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1873932%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20formatting%20-%20mix%20of%20slash%20and%20dash%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1873932%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F855942%22%20target%3D%22_blank%22%3E%40Averheim%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EText%20To%20Columns%20affects%20only%20texts%2C%20if%20dates%20are%20inside%20they%20won't%20be%20changed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

 

2020-11-03_14-31-24.png
Since changing my laptop, all excel exports from our OM-system looks horrible in regards to the date format. The big issue is that all regions enter their dates differently in our OM system. I have no idea what my old Excel installation was set up to make it work, I simply can't get my head around how to format all dates at once without changing all slashes to dashes. Even if I would change the Slashes to Dashes I still have the problem of the order of the dates as "slash and dash-dates" has a different ordering. It would be nice if the formatting could be done in Excel and not by tempering with the date settings of my PC... An example file is attached. 

2 Replies

@Averheim 

(i'm using a non-English system . Guess what i mean if my translation guesses are totally wrong...)

 

a formula solution might be:

=DATEVALUE(B2)

But that only works if your system (windows) is set to US-dates

 

Another way that usually works: 

Select column B

Data->Text to Columns

[Next]->[Next]

Now you should be able to select DATE and the format MonthDayYear MDY  (in-data format. Not what you want).

[End]

If you are lucky the Text-dates (/) are transformed to real dates and the "real dates " are untouched.

Chang the display-format If necessary (Ctrl+shift+3  Or right click. Format cells, Nubers format. Date)

 

The problem:

Some of your dates are "real" excel dates (-). And some are text (/)

select column, Right click, Format cells, alignment, Horizontal=General

Now you will se that the text is aligned to the Left and the "real" Dates to the right. 

Or change display-format of the column t general (Ctrl+0). The text-dates will still be text but the real, calculable dates will show upp as days since 1900-01-01  (44161)

 

You can use a formula that checks if its a date (number) or a text and only fix the text-Dates

=IF(ISNUMBER(B2),B2,DATE(MID(B2,7,4),MID(B2,1,2),MID(B2,4,2)))

But i think that should give the same result as method 1

 

else. Use power query with "Locale": 

https://support.microsoft.com/en-us/office/internationalization-power-query-d42b9390-1fff-413f-8120-...

Expand: Use a non-default locale setting on a Change Type operation

 

@Averheim 

Text To Columns affects only texts, if dates are inside they won't be changed.