free online microsoft training

Free tips and tricks for using Microsoft Office and Windows

How to convert text to date format in Excel

Print Friendly, PDF & Email

Last week I received an SOS from a good friend. She had data that was exported from an LMS and being used for analysis within Excel. A particular column contained a date and time in each cell that needed to be filtered and sorted by month. This was proving impossible as the data was being treated as text rather than Excel recognising the date formats. So how on earth can we convert the text to a date format?

Dates are stored as text in Excel

To sort out this situation we needed to use two different methods: Text to Columns, and Filtering.

Separate data using Text to Columns

One of the great features you can utilise when trying to clean up data in Excel is the Text to Columns feature. This tool allows you to specify a specific delimiter such as a tab, comma or semicolon and use the delimiter to identify where your data should be split. This results in data that may have been located within one column, separating and being displayed across 2, 3 or more columns.

The first thing we are going to do is separate the date and time information which is delimited by a comma.

  1. Highlight the column containing the data you wish to split
  2. Select the Data tab and choose Text to Columns

Click the Data tab and click the Text to Columns button

  1. The Convert Text to Columns Wizard – Step 1 of 3 window will appear:

The Convert Text to Columns Wizard will start

  1. Make sure that the Original Data Type is set to Delimited
  2. Click Next
  3. In this particular example, the date and time are separated by a comma, so I will select the Comma delimiter type and untick the Tab delimiter:

Step 2 of the wizard will be shown

  1. You will see the Data preview window at the bottom shows a dividing line appears between the date and time data
  2. Click Next
  3. The final step of the wizard asks what data format you’d like the new column to use, leave it as General and click Finish

The final step of the wizard will appear

  1. The date and time is now split into separate columns

The date and time is now displayed in separate columns

  1. Automatically Excel recognised the dates and has formatted them as such
  2. In my example, the dates are automatically using US date formats, I’m in Australia so I want these showing in our date format
  3. Highlight the column containing the dates
  4. From the Home tab click the Number Format drop-down menu and choose Short Date or Long Date format

Change the date format as needed

  1. The date formats are now corrected

Filter dates by month

Now that I’ve cleaned up the data set, I can now use the Filter functions within Excel to filter out dates by specific month.

  1. Click within the data area and click the Data tab and turn on the Filter button
  2. The Filter drop-down arrows will appear on each column of data

Enable filtering for Excel

  1. Click the drop-down arrow for the column you wish to filter by month
  2. You will see the records are shown grouped into year and month options

You can filter by year and month

  1. Remove the tick from the months you do not want to include in your filtered data set. I only want to see records for January 2021, so I untick the February checkbox and click OK
  2. I can now only see records from January
  3. Your data is now filtered by month
  4. To bring all data back, or remove the filter, go back into the drop-down menu for the column you have filtered
  5. Click Clear Filter From … from the options

I hope this has helped to understand how to use the Text to Columns function and perform a basic filter based on dates within Excel. If you have any questions or comments please post them below.

Share on facebook
Facebook
Share on google
Google+
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on pinterest
Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *

5 × four =

This site uses Akismet to reduce spam. Learn how your comment data is processed.