Dates changed to number

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



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 @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? 


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