Forum Discussion

aie_102000's avatar
aie_102000
Copper Contributor
Sep 04, 2020

Dates changed to number

I have a 10-year-old excel sheet database. Whilst working on it, the dates of more than 1000 entries suddenly changed from DD/MM/YYYY to random numbers. When I click on the cell itself, the correct date in the correct format appears in the function box but would still be a random array of numbers in the actual cell. I've tried to click on format and change it to Date, Number then Date, different types of dates, but nothing changes. I've tried doing it on one, multiple and all cells, and nothing changes. 

Can anyone help?

excel
Formulas and Functions

3 Replies

  • jukapil's avatar
    jukapil
    MCT
    Sep 04, 2020

    aie_102000 

     

    Hi Ahmad

     

    Do one thing, first write 1 in any cell > then copy that cell > Go to Date Column > Right Click > Paste Special >Click Multiply > Click Ok

     

    Once you follow this step. Then Select Column again and change the number formatting to Date. This will work

     

    Thanks

    Kapil

     

    • aie_102000's avatar
      aie_102000
      Copper Contributor
      Sep 04, 2020

      Thanks jukapil
      Unfortunately that didn't work.

      I copied 1 into a cell right beneath the dates column and then followed the steps you listed but nothing happens.
      It's worth noting that the format tab does show "Date" and not "Number" or "General". Which is why it's odd. The format is "Date" already, the function tab shows the correct date in the correct format, but the cell shows a string of numbers.

      Any ideas? 

Resources