SOLVED

Entering dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2507347%22%20slang%3D%22en-US%22%3EEntering%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2507347%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20my%20cell%20format%20set%20to%20Date%2014%2F03%2F12.%20However%2C%20when%20I%20enter%20a%20date%2C%20Excel%20entirely%20changes%20it.%20For%20example%2C%20if%20I%20enter%20010121%20(for%201%20January%202021)%20it%20comes%20up%20as%2016%2F09%2F27.%20Do%20I%20have%20to%20enter%20%2F%20between%20the%20day%2C%20month%20and%20year%20for%20every%20entry%3F%20That's%20very%20tedious%20...%20What%20am%20I%20doing%20wrong%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2507347%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2507404%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2507404%22%20slang%3D%22en-US%22%3Eshort%20answer%20is%20yes.%20you%20could%20use%201-1-21%20if%20you%20prefer%20but%20otherwise%20excel%20just%20thinks%20you%20entered%20a%20number%20010121.%20if%20you%20want%20you%20can%20convert%20all%20your%20dates%20to%20days%20so%20entering%2044197%20would%20give%20you%201%2F1%2F21%20and%2044198%20will%20give%20you%201%2F2%2F21%2C%20etc...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2507423%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2507423%22%20slang%3D%22en-US%22%3EOK!%20Thank%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2507440%22%20slang%3D%22en-US%22%3ERe%3A%20Entering%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2507440%22%20slang%3D%22en-US%22%3E%3CP%3EAn%20additional%20note%3A%3C%2FP%3E%3CP%3EAs%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3Bmentioned%2C%20Excel%20stores%20dates%20as%20numbers%20starting%20with%201%20being%201%2F1%2F1900.%26nbsp%3B%20So%2C%2010121%20is%20the%2010121st%20day%20since%201%2F1%2F1900%20(a.k.a.%209%2F16%2F1927)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

6 Replies
best response confirmed by allyreckerman (Microsoft)
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...
OK! Thank you.

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)

@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.

Thank you!
OK. Thank you.