Change dates formatted as text to dates

If your worksheet has dates that were perhaps imported or pasted that end up looking like a series of numbers like in the picture below, you probably would want to reformat them so they appear as either short or long dates. The date format will also be more useful if you want to filter, sort, or use it in date calculations.

column of dates in text format

  1. Select the cell, cell range, or column that you want to reformat.

  2. Click Number Format and pick the date format you want.

The Short Date format looks like this:

change data to short date format from ribbon

The Long Date includes more information like in this picture:

button on the ribbon to change to long date format

You can also do this by using the DATEVALUE function.

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.