Excel - Dueling Excel 145 - Dateify with 0000/00/00 - Episode 1840
3,528 views · Published 20 December 2013 · 6:32 · Indexed 8 November 2026
Channel: MrExcel.com · 2013 · Education
Microsoft Excel Tutorial: Dueling Excel 145 - Dateify with 0000\/00\/00. Welcome back to another episode of the Dueling Excel podcast! In this episode, we will be tackling the challenge of converting strange dates into their proper format. Join me, Bill Jelen from MrExcel, and my co-host Mike Girvin from Excel Is Fun, as we explore different methods to solve this problem. The dates we are dealing with are in the format of year, month, day, and we need to convert them into real dates. I will be using the data text to columns feature, while Mike will be using the text function and custom number formatting. Both methods have their own advantages and we will see which one works best for this particular scenario. Using the data text to columns feature, I simply select the data and go to [alt D E] and choose the option for data text to columns. In step 3, I make sure there are no delimiters and select the YMD format. This quickly converts the strange dates into their proper format. Mike, on the other hand, uses the text function and custom number formatting to achieve the same result. He learned this trick from Barry Houdini on the MrExcel Message Board and it's definitely worth checking out. But wait, there's more! Mike also shows us how to use the Flash Fill feature to convert the dates. This feature can be a bit tricky when dealing with lead zeros, but with some formatting and a couple of examples, it can be a useful tool. However, we both agree that the data text to columns feature is the way to go for this particular problem. In the end, we both learn something new and give credit to Barry Houdini for his amazing trick. So, join us for another exciting episode of the Dueling Excel podcast and don't forget to check out the MrExcel Message Board for more amazing tips and tricks. Thank you for watching and we'll see you next time! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Converting a date format using "0000\/00\/00" (00:15) Topic: converting strange dates into real dates (00:25) Using data text to columns to convert dates (00:35) Formatting the data and clicking "finish" (00:50) Another method: using the equal date function (01:01) Using the LEFT, MID, and RIGHT functions to convert dates (01:29) Discussion of different methods (03:15) Chad from Microsoft's input on using flash fill with numbers (03:30) Mike's method using custom number formatting and the text function (05:24) MrExcel's reaction to Mike's method (06:00) Giving credit to Barry Houdini for the trick (06:12) Points awarded for different methods (06:22) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Barry Houdini Data text to columns Date conversion Excel Is Fun Excel tricks Flash fill Number formatting Text function YMD format Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153665/ In this dueling Excel podcast, the goal is to use a formula to convert 20131220 to a real Excel date. We'll meander through the usual suspects: Text to Columns, DATE(MID), Flash Fill and then be amazed as Mike provides a crazy TEXT function to solve the problem.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920