Formatting date

[GL2021]

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

[#2]

@GL2021 

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

[#3]

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.

[#4 · Accepted solution]

@GL2021 

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 http://www.cpearson.com/Excel/DateTimeEntry.htm 

[#5]

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

[#6]

@GL2021 

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