Excel Cells not formatting to date

Question

I am unable to format a column into date format unless I click into the cell then click out.

This data is exported from a third party application to .xlsx format. When I open the file in Excel and try to format what should be the date column, the date formatting will not take affect until I click into the cell as if I were going to make changes then exit/click out of the cell.

Image 1 raw data

image 2 setting the formatting

image 3, clicking into a cell.

image 4, clicking out of the cell.

Notice the rest of the rows in image 4 that rows 4 and beyond have retained their original formatting, and the only ones that have the new formatting are the ones I clicked into then clicked out. Selecting a cell without going into it also keeps the original formatting. While clicking into a cell then out is not a huge problem, when you get thousands of rows this gets annoying. Any fix or workaround you can suggest?

That is because they are imported as text that look like dates and not true dates. You can use text to columns to convert wholesale. — Scott Craner, Mar 09 '18 at 20:38
Select the whole column run text to columns, walk through the steps, on the last step choose date. hit finish. — Scott Craner, Mar 09 '18 at 20:42

score 2 · Answer 1 · answered Mar 09 '18 at 20:46

2

I used to "Text to Column" wizard and converted the data into dates.

answered Mar 09 '18 at 20:46

ProjectPokket

155
1
3
14

Excel Cells not formatting to date

1 Answers1