I was given an Excel document.

In the data there is a column of dates formatted 5/20/2014. Half the data in that column is formatted as a date. The other half is formatted as text. When I try and format the entire column as a text the cells formatted as dates become their numeric equivalent (integers). If I try and format the entire column as a date, the cells formatted as text stay text values until I double click the cell and hit enter; then they become formatted as a date.

How do I change the format from text to date without having to double click in thousands and thousands of cells?


Use the formula =IFERROR(DATEVALUE(A1),A1) (where A1 is the beginning cell of your date column) at the top of a new column, fill it down to the end of your table, then copy the new column and use Paste Special -> Values. Format the new column as Date.


There is more than one answer, but I would copy from the last date cell that is correct, Click the cell and hit ctrl-c. Then use your favorite method to select the destination cells, unless there are 40,000 rows I usually just click the first cell hold the click and drag to the end of the range, then use PASTE SPECIAL and select formats.

You could also copy a single cell, then select the entire column that cell is in by clicking it's header... again use paste special and formats.

The advantage to copying an existing format is if there are other setting to the format than just date mode that were set, they will also be copied.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.