Date Formatting Not working

**SallyW-EDUK** · 10-16-2012, 06:07 AM

I am using excel 2010 and having issues with the date formatting.

I have formatted a column to be dd/mm/yyyy however when I type in a date it appears wrong e.g. if I put in 130383 (wanting excel to convert it to 13/03/1983) it appears as 21/12/2256. I have tried everything including using a custom setting for formatting cells and ensuring my region and language settings on the control panel of my computer are correct and but nothing seems to work!

Any help would be greatly appreciated.

**TMS** · 10-16-2012, 06:18 AM

You will always need to put in one or other separator so that Excel recognizes the input as a date. In this case, it thinks the number is a date in the future.

Regards, TMS

**BiIntel** · 10-16-2012, 06:20 AM

Hey

Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. So when you enter 130383, excel takes it as 130,383 days passed since start of the year 1900 and displays the according date.
I guess easiest is to enter date with a "/" 13/03/83.

Cheers
BI.