How to Change Excel Serial Date Format Using Text to Columns

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.1

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.2

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.3

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.


Tags


You may also like

December 20, 2013

100+ Best Excel Resources And Websites

August 6, 2019

How to Create a Relative File Path in Power Query

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

The Excel Rollover Mini FAQ

February 17, 2014

It’s time to say “Goodbye,” to Hungarian Notation
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.

>