SOLVED

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

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

Albertoalej_1-1672160052188.png

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

Albertoalej_2-1672160133271.png


After Clicking Enter

Albertoalej_3-1672160179351.png

 

5 Replies

@Albertoalej 

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

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

 

best response confirmed by Albertoalej (Copper Contributor)
Solution

@Lorenzo 

Didn't work, even when formatting as Date

Albertoalej_0-1672162912567.png

 

@Albertoalej 

 

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...
1 best response

Accepted Solutions
best response confirmed by Albertoalej (Copper Contributor)
Solution

@Lorenzo 

Didn't work, even when formatting as Date

Albertoalej_0-1672162912567.png

 

View solution in original post