Excel doesnot recognize the date until duble click

[Abdullah Omam]

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

[#2]

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 regards

[#3]

@cygent 

Data->Text to Columns with MDY on third step converts that correctly

[#4]

My apologies @Sergei Baklan 

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]

[#5]

@cygent 

These are separate. First, most probably you have dates as texts and you need to convert them to dates (actually they are numbers in Excel). You may check do you text or date by using =ISTEXT(A1) from any empty cell.

Once you converted texts to dates you may apply any desired format to them, that's only visualization of numbers behind. 

In these two cells is the same date, but to the second I applied another format

[#6]

Thank you Sergei,

But that begs the question.. When I am copying as text & pasting, why are only some values getting pasted as text whilst the others as numbers? That is what is baffling me!!

If it was one or the other I can understand & easily format the entire range, but Excel is only converting some of them, which makes it almost impossible to format!

Any guesses/ideas/clues anyone? Please, thank you!

Kind regards,
Nikhil Prabhu

[#7]

@cygent 

If you copy/paste from other source Excel automatically converts what it could recognize and keep unchanged the rest. If you copy/paste from source where dates are in another locale, better to format destination column as texts, paste values to it, apply general format and convert text to dates after that.

[#8]

Ok, Thank you

[#9]

in funcion sumar.si cant usad

[#10]

Worked like a charm. Thanks Wyn!

[#11]

Thank you so much. I was struggling for hours :)

[#12]

hi
I have an excel file
which is in one of the columns of that time
In this case, I cannot get the average from it, and I have double click on each cell twice to be able to get the average from them.
Please advise

[#13]

Lifesaver

[#14]

saved me lots of clicks

[#15]

you sir are the MVP.... THANK YOU!

[#16]

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"

[#17]

cool! that works when i try to convert from text to date in a custom format of DD.MM.YYYY.

[#18]

Did you reach a solution ?
I didn't solve it yet.

[#19]

These response assisted me and saved a day