Formatting date

New Contributor

I try to format a column to be a date column (example 3/20/20).  But when I enter a date it appears as a different one.  Help!


Office 365 Windows 10

5 Replies


Could you provide examples, and/or attach a sample workbook demonstrating the problem?

I used the 'format cells' option and chose "date" using the mm/dd/yy option. But when I enter a date as mmddyy (ex 31420) it changes it to mm/dd/yy but as another value such as 4/13/72.
best response confirmed by GL2021 (New Contributor)


You cannot enter dates without separators in Excel - it doesn't recognize 31420 as a date; it sees it as a number.

If you want to be able to enter dates without separators and have them converted to 'real' dates, you need VBA - see 

I've never had this problem before this version of Excel. Was this a change to this version?


No, Excel has never accepted dates without separators in any version.