Entering dates

[LIz_CC]

I have my cell format set to Date 14/03/12. However, when I enter a date, Excel entirely changes it. For example, if I enter 010121 (for 1 January 2021) it comes up as 16/09/27. Do I have to enter / between the day, month and year for every entry? That's very tedious ... What am I doing wrong??

[#2 · Accepted solution]

short answer is yes. you could use 1-1-21 if you prefer but otherwise excel just thinks you entered a number 010121. if you want you can convert all your dates to days so entering 44197 would give you 1/1/21 and 44198 will give you 1/2/21, etc...

[#3]

OK! Thank you.

[#4]

An additional note:

As @mtarler mentioned, Excel stores dates as numbers starting with 1 being 1/1/1900.  So, 10121 is the 10121st day since 1/1/1900 (a.k.a. 9/16/1927)

[#5]

@LIz_CC Excel sees your entry as number 10121. Formatted as a date it will become the 10121th day counted from January 1, 1900. Hence,  16 September 2027. If, for example, your Excel recognises English dates you can just enter 10jan in a cell that is formatted as dd/mm/yyyy and it will show as 10/01/2021.

Edit: The system seems to be slow today. Didn't see any of the above until after I posted my response.

[#6]

Thank you!

[#7]

OK. Thank you.

[#8]

While I understand the desire to have completely unique identifiers for dates, there is no way I'm memorizing the number of days since January 1900. How can I get Excel to show the dates in the format I chose? I'm getting very frustrated!