Excel will not format date

Question

Hi there,
I cannot get my Excel to format as a date. I format the cell to display the date as MM/DD/YY. I enter 010121 so it can format as 01/01/21. Instead 09/16/1927 is displayed and it is in the same in the formula bar. If I try 01012021 it displays as 12/25/1970 and the formula bar shows 10/25/4670.

I've tried all that I can think of. Settings in my mac are correct. I've tried changing the preferences under Excel, and no luck.

I appreciate any help.

Best,
Lindsey

Answer 1

@Lindsey Barigian

You can pre-format your cells with Custom Format: 00/00/00
However, when entering your numbers there should be no ambiguity:

010322 works
10322 works
01322 doesn't work
1322 doesn't work
10322 works

So, if you adopt mmddyy that should always work...

Answer 2

When entering dates they must include delimiters between the Month, Day and Year. If you simply enter a number like 010121 then Excel sees it as a number and then to display as a date it counts from 1/1/900 in days and displays the date.

The Number format for dates is for changing valid dates into an alternative date formats; not to convert a number entered without the delimiters between the Month, Day and Year.

Unfortunately Excel does not support a masking system like in Access where you assign a mask to a field and typing the dates without delimiters enters them correctly with the delimiters.

Answer 3

@Lindsey Barigian ,

As OssieMac said, you need to use delimiters to let Excel know recognize data as Month, Day and Year,

Otherwise, you could enter the 010121 first as general format or '010121 as a text, then use Text to Columns to transfer text as date format.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.