Forum Discussion

Abdullah Omam's avatar
Abdullah Omam
Copper Contributor
Jan 04, 2017

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...
excel
Formulas and Functions
Wyn Hopkins's avatar
Wyn Hopkins
MVP
Jan 05, 2017

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

 

cygent's avatar
cygent
Copper Contributor
Sep 14, 2021
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
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 14, 2021

    cygent 

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

    • cygent's avatar
      cygent
      Copper Contributor
      Sep 15, 2021

      My 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]

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 18, 2021

        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

Resources