Convert SAP Dates to Real Dates in Excel. Excel Magic Trick 1676

Learn how to DD.MM.YYYY into real dates using a worksheet formula or Power Query.
Topics:
1. (00:00) Introduction.
2. (00:21) Regional Settings
3. (00:43) SUBSTITUTE worksheet function when regional settings and dates in column are the same structure.
4. (01:45) DATE, RIGHT, MID, LEFT worksheet functions when regional settings and dates in column are the NOT same structure.
5. (02:56) Power Query solution using “Using Local” Data Type.
6. (04:17) Summary and End Video Links

[ExcelVbaIsFun]

Too cool for school! Actually, I never remember school learning being as fun as watching your videos! Thanks for the great content!

[sanguinj]

Wow...You hit the nail on the always wanted to know how to do it using power query. That saves tons of time.

[akademipowerpointmalaysia]

I love the way you teach! Easy to understand. Thank you so much!

[jrdvalente]

Before seeing this video, I was thinking to use the function substitute and multiply by 1. Thanks for this video.

[mattschoular]

Thanks Mike...Another great video to save many users potentially so much frustration.

[edge]

I just love Excel because of the things I've learned from you Mike! Thank you very much!

[DougHExcel]

Using Locale....a very interesting find! Thanks Mike

[enriquedominguez]

I liked all the solutions, more valuable tools. Thank you Mike.

[arturoguzman]

Mike you are super amazing, like power query 😎

[timmyjan]

one way i do it is to select the column then click on DATA tab >Click TEXT TO COLUMNS> Click Next on Delimited>uncheck everythign on the next screen click Next> Click the Date Select the DMY or MDY ( this prints the dates the way you want)> Finish.
I learned three new ways to change the text dates now too :D Excel is Fun

[johnborg]

Thanks Mike. All Three were GREAT!!! Formulas update instantly but you have to update with Power Query. However if you have large Query is the way to go!!! : ) : ) Thanks again.

[coolhappyblue]

You sound like Ross from 'Friends' ! :-) Very friendly voice

I tried another option -
1. Select the date column (or a copy of that column). Then use CTRL+H to manually search/replace '.' with '/'. Then right-click the date column, then 'Format' > 'Number' tab > Category=Date > Type='14/03/2012'.
2. Select the time column. Then in the ribbon, go to 'Home' > 'Number' group > set the 'Time' type

[Softwaretrain]

Great and useful topic.
Thanks for the solutions.
Also there are some other solutions:
1.replace "dot" with "slash" depends on windows setting can be one easy solution.
2.flash fill will be handy sometimes.
3.text to column in data tab and next>next>choose Date DMY>finish

But normally I use formula that you present.
Thanks.

[chrism]

Fantastic video Mike. I have never used Locale before, great tip!

[simfinso]

Very Easy ways shown Thanks.power Query is the Best for this type problem

[alexkim]

Ah! I never knew of such function available. The use of "locale" is brilliant. Thanks.

[darrylmorgan]

Boom!The Awesome Power Query And Locale Data Type Definietly Way To Go..Great Tutorial Thank You Mike :)

[ogwalfrancis]

Wao, all three methods are Awesome, thanks Mike

[harichowdhury]

Thanks for this awesome video. I was trying find the a solution but couldn't. This video has saved my time a lot on one my reports.

[rrrprogram]

Awesome mike... I generally use find and replace...