Forum Discussion
Excel doesnot recognize the date until duble click
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"
- saved me lots of clicks
- Lifesaver
- Thank you so much. I was struggling for hours 🙂
- Worked like a charm. Thanks Wyn!
- Hi @Wyn: I now have a related of not similar problem. Please try copy pasting 01/01/2021
01/15/2021
01/30/2021
02/01/2021
02/15/2021
02/28/2021
03/01/2021
03/15/2021
03/30/2021
04/01/2021
04/15/2021
04/30/2021
05/01/2021
05/15/2021
05/30/2021
06/01/2021
06/15/2021
06/30/2021
Now the problem you will notice is ONLY some values are getting altered as 01-01-2021
02-01-2021
03-01-2021 !!
I am at my wits end what to do now? None of the solutions are working! Please help. & sorry if I am diverging the thread, please bear with me, I apologize!
Thank you & Kind regardsMy apologies SergeiBaklan
I mean I actually I want values in the "/" format only!! I do not want the - format, I don't know why only some values get converted when I am cutting and pasting all the mm/dd/yyyy values ?!? Any guesses?
Thank you
[Sorry I wasn't clear]
Hi Wyn Hopkins
I know I am a few years late to the conversation, however I am faced with the same issue.
I've adopted your solution, within the workbook, to "to highlight the column of dates and then select Data > Text to columns > Finish".
I am automating reports for clients and wondering if this command above, or something similar, can be implemented into a snowflake script to ensure that once the code has run, results are exported to a CSV file, that the file opens with the timestamp date showing instantly... rather than asking the client to do the manual workaround you have mentioned!
any help would be great (very new to the community).
Thank you
Hi Sanders8891
Welcome to the tech community, for future notice it's best to start a new thread and a link to to any previous solution.
I'm not sure what a snowflake script is sorry. I'd utilize Power Query for repeatable simple data transformation. If automate export to CSV is required I'd then couple it with some VBA.
Wyn Hopkinsworked like a charm. Thanks!
God Bless you Wyn Hopkins
