In Excel, I have a set of dates in a column. However, some have been added as dates, others have been entered as text. I only found this out when I was attempting to work with the dates and got an error message in some cells. How can I sort in date order when some of the cells aren’t dates? Darren Young
The short answer is that you can’t. What you can do is to create another column where you convert the cells so they’re all dates. You can use a formula such as this:
=IF( IS TEXT( A 1), DATE VALUE( A 1), A 1)
What this formula says is that if the value in cell A1 is text, then it should be converted to its equivalent date value. If it isn’t text, we can then assume that it’s a date, so you can pull it across without making any changes to the cell. Once you’ve done that, you can then sort on your new date column.
⬆ You can use IsText and DateValue to convert text dates to true dates in Excel