Why do I have to hit enter for format to work?

New Contributor

Okay so I remember having this question back in college, but never actually had the interest to google this question.

So I have the following data (non-table), And in order for the Date format to work, I have to click the cell, and then hit enter individually for each cell. Is there a way around this? I have the Workbook Calculation set on Automatic.


The E column is a simple concatenate function, from which I then copy the entire column and then paste it as value. Which results in the column F, but as you can see it is not date formated, I have to click the cell and then its formula bar and hit enter for it to work.

Before Clicking Enter


After Clicking Enter



5 Replies


Try the following in E2 and format the cell as Date:



best response confirmed by Albertoalej (New Contributor)

@L z. 

Didn't work, even when formatting as Date





In column B:

Sans titre.png

could you replace Januay with January and see how it goes...?

omg, I had already done the manual approach so I had to recreate the excel sheet just to try your approach and missed that letter. Did It this way and It now works, thanks a lot!
You're welcome, glad I could help
There's a mark as solution link at the bottom of each reply you get - This helps those who search
Thanks & Nice day...