Dates changed to number

Highlighted
Occasional Contributor

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?

3 Replies
Highlighted

@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

 

Highlighted

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? 

Highlighted

@aie_102000 Can you share few samples in sample file. I may help upon it