A Cheat Sheet to get more out of Microsoft Excel
Ever wanted just the Cliff Notes version of Excel? We've got you covered with the Excel Power Users Quick Guide - and it's free.
Microsoft Excel allows us to show any complex data in an easy way. You could only see some format changes when you extract the data from the database. For example, say you have the data related to employee related to joining date. Then, when you extract this data from the database, then you would see data related to date will be shown as a string in Excel sheet.
This format is not understandable by the normal user and we need to change excel serial data to date format. So, Excel MVP Jordan Goldmeier is here to help us. In this article, we will see how to change excel serial data to date format using text to columns.
As said earlier, when you extract the data from the database, you would see the wrong date format in the form of a string. We use simple text to columns trick to convert that into correct date format. Here we go!
In the first step, you need to select all the entries of the wrong date format which you want to convert into correct date format. You could see that all entries of wrong date format are left aligned mentioning that they are in string format.
While all entries of wrong date format are selected, click ‘Text to Columns’ option in ‘Data Tools’ under ‘Data’ tab. This would open up the ‘Convert Text to Column Wizard’. Now, click on “Finish” button without selecting any new options. You could see that data being right aligned now.
Now, select the data and choose “Short Date” from the drop-down box in ‘Number’ category under ‘Home’ tab. You could see the wrong date format data has been now converted to correct date format.
This is the easy and simple way to change excel serial data format using text to columns. This avoids copy and paste method. Let us know if there are any ways to convert serial data to date format in an easy way.
Hope this helps you and if you have anything to add, please do share with us through comments.
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Get the FREE Excel Power User Guide