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

[Albertoalej]

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

[#2]

@Albertoalej 

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

=DATEVALUE(CONCAT("01","-",B2,"-",A2))

[#3 · Accepted solution]

@L z. 

Didn't work, even when formatting as Date

[#4]

@Albertoalej 

In column B:

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

[#5]

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!

[#6]

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...