Jan 04 2017 05:01 AM - edited Jan 05 2017 04:14 AM
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
Jan 04 2017 08:21 PM
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
Jan 04 2017 09:31 PM
I tried these ,Unfortunately they are not working too ,
Jan 04 2017 09:45 PM
are you able to attach a file containing the problematic column?
Jan 05 2017 03:15 AM
I already attached file in the first post
Jan 05 2017 03:29 AM
IMHO, the trick which Wyn mentioned shall work if the date format in regional settings is the same as text representation of dates you entered. Other words, Hijri system calendar plus office package with Arabic SKU. But that only my guess, i'm in Georgian calendar and can't reproduce the issue...
Jan 05 2017 04:27 AM
Jan 08 2017 05:22 AM
so , is there any way to solve the issue ?
Jan 08 2017 03:36 PM
Not exactly sure how to resolve it. You can start by seeing if there's any obvious issues in your system / region settings
Jul 16 2019 05:29 PM
@Wyn Hopkins This worked perfectly! Thank-you so much. You saved me hours and tons of frustration!
@Wyn Hopkins wrote: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
Dec 05 2019 10:46 PM
Thank you, I had the same problem, only one trick worked.
I copied blank cell then highlight the dates and Paste Special > Add. The date changed to number format, ##### then I formatted the cells in to my preferable date format dd-mm-yyyy.
Hope this will work for everyone.
Oct 19 2020 10:01 AM
@Wyn Hopkins I went through registration procedure just to say thank you! ))
Oct 23 2020 03:43 AM
Oct 27 2020 04:09 PM
@Wyn Hopkinsworked like a charm. Thanks!
Mar 17 2021 03:05 AM
God Bless you @Wyn Hopkins
Aug 09 2021 10:27 PM
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
Aug 09 2021 11:25 PM
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.
Aug 10 2021 06:56 AM
Aug 10 2021 03:29 PM
@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