SOLVED

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

@GL2021 

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)
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 

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

@GL2021 

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