Forum Discussion
Excel doesnot recognize the date until duble click
I have problem in Excel , I copied dates from internet report to Excel but Excel does not recognize them as date till I press double click and Enter in each cell of the date column
I tried many ways I found in the internet ,but no one of them was usefull for me.
the ways which I tried " copy and paste special" & "Text to columns "
the format in the cells is correct ,so I do not know how to fix it because the data is so big in the column
38 Replies
- in funcion sumar.si cant usad
- My solution would be to highlight range, select the find/replace and change all the - to /
cool! that works when i try to convert from text to date in a custom format of DD.MM.YYYY.
- Worked! Easy-peasy.
AlexWaterton Wyn Hopkins thank you both for responding. Given I need the amendment to the data to occur without me opening the sheet, I think I will have to create a macro and incorporate it into my sql (run via snowflake).
hopefully it will work!
thanks guys
Hi Abdullah
One possible solution is to highlight the column of dates and then select Data > Text to columns > Finish
This "tricks" Excel into treating text as numbers
Alternatively copy a blank cell then highlight the dates and Paste Special > Add
This again tricks Excel into adding 0 to each cell and therefore the text gets converted to values
Thank you - so easy in hindsight (ref: text to columns above). Appreciated!
These response assisted me and saved a day
- I had nearly 200K date of births in format 01/Nov/1972 that I was not able to convert to date format I prefer - 01/11/1972 until I double click the column. To do this manually for 200K+ records would be time consuming and wastage. Thanks for your suggestion above Wyn Hopkins, this really helped - highlight column and "select Data > Text to columns > Finish"
