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.

Steps to Change Excel Serial 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!

1 – Select All Entries

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.

2 – Convert Text to Columns

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.

3 – Select the Short Date

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.

What’s next?

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.

• Author
• Recent Posts

Sridhar Belide

Sridhar Belide is a skilled software engineer with over a decade of experience in the industry. Currently working at Wells Fargo since December 2018, he focuses on developing UI frameworks for front-end applications using React. Before joining Wells Fargo, Sridhar spent five years and four months at Tata Consultancy Services, where he held the position of IT Analyst. He also has experience as a Community Manager for Impact SoftTech Pvt. Ltd. EALP and as a Software Research and Development Engineer at NSN - Nokia Solutions and Networks. Sridhar holds a Bachelor of Technology (B.Tech.) in Computer Science from JNTUH, demonstrating his strong foundation in the field.

Latest posts by Sridhar Belide (see all)

• Edit Excel Formulas in WORD – Spreadsheet Tips and Tricks - February 17, 2022
• Using the Immediate Window in VBA - April 4, 2017
• Intersection Operator with Named Ranges – Excel Tips and Tricks - October 3, 2016