Excel doesnot recognize the date until duble click

Copper Contributor

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

37 Replies

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

 

I tried these ,Unfortunately they are not working too ,

are you able to attach a file containing the problematic column?

I already attached file in the first post

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

Sorry Abdullah, I missed that file

 

 

@Sergei Baklan's answer may be the key

 

 

so , is there any way to solve the issue ?

Not exactly sure how to resolve it.   You can start by seeing if there's any obvious issues in your system / region settings

 

Region Settings.PNG

@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

 


 

@Wyn Hopkins 

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.

@Wyn Hopkins I went through registration procedure just to say thank you! ))

@nkatetnikov ,  thanks for making the effort 

 

@Wyn Hopkinsworked like a charm. Thanks!

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.

My solution would be to highlight range, select the find/replace and change all the - to /

@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

Worked! Easy-peasy.