Excel DateTime Entry

%3CLINGO-SUB%20id%3D%22lingo-sub-2365186%22%20slang%3D%22en-US%22%3EExcel%20DateTime%20Entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365186%22%20slang%3D%22en-US%22%3E%3CP%3EI%20used%20to%20be%20able%20to%20enter%20a%20Date%20and%20Time%20combination%20into%20a%20single%20cell%20and%20it%20would%20return%20a%20valid%20date%20and%20time%3A%26nbsp%3B%20I%20would%20enter%3A%26nbsp%3B%2023%20June%2010%3A15%20into%20a%20single%20cell%20and%20it%20would%20populate%20it%20with%20a%20correct%20date.time%20number.%26nbsp%3B%20The%20cell%20is%20custom%20formatted%20to%20d%20mmm%20hh%3Amm.%26nbsp%3B%20It%20no%20longer%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2365186%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2365248%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20DateTime%20Entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365248%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1056982%22%20target%3D%22_blank%22%3E%40TM415%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20still%20works%20for%20me.%20Have%20you%20changed%20your%20system%20date%20and%20time%20settings%3F%3C%2FP%3E%0A%3CP%3EDoes%20the%20problem%20persist%20if%20you%20quit%20and%20restart%20Excel%3F%20If%20you%20restart%20your%20computer%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2365373%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20DateTime%20Entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365373%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20reply.%3CBR%20%2F%3EMy%20date%2Ftime%20settings%20on%20the%20computer%20are%20correct.%3CBR%20%2F%3EYes%2C%20the%20problem%20is%20in%20the%20spreadsheet.%20When%20I%20enter%20the%20custom%20d%20mmm%20hh%3Amm%20date%20time%20it%20turns%20the%20entry%20to%20text%20format.%20The%20cell%20is%20still%20registered%20as%20%22Custom%22%20but%20the%20entry%20is%20now%20text.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2365555%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20DateTime%20Entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365555%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1056982%22%20target%3D%22_blank%22%3E%40TM415%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20number%20format%20doesn't%20matter%20(as%20long%20as%20it%20isn't%20Text).%3C%2FP%3E%0A%3CP%3EWhen%20you%20enter%20something%20in%20a%20cell%2C%20Excel%20asks%20the%20operating%20system%20whether%20it%20recognizes%20it%20as%20a%20date%20according%20to%20its%20date%2Ftime%20settings.%20Apparently%2C%20your%20system%20doesn't%20recognize%20'23%20June%2010%3A15'.%20On%20an%20English%20language%20system%2C%20it%20should%20recognize%20it%20as%20far%20as%20I%20know...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2381128%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20DateTime%20Entry%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381128%22%20slang%3D%22en-US%22%3EI%20discovered%20I%20can%20get%20Excel%20to%20recognize%20date%2Ftime%20entry%20as%20a%20valid%20combination%20if%20I%20type%20the%20hh%3Amm%20first%2C%20followed%20by%20d%20mmm%20yy.%20So%2C%20full%20syntax%20is%3A%20HH%3AMM%20D%20MMM%20YY%20works.%20If%20I%20do%20not%20try%20to%20include%20a%20time%20then%20any%20combination%20of%20day%2C%20month%20year%20seems%20to%20be%20recognized%20by%20Excel%20and%20a%20date%20number.%3C%2FLINGO-BODY%3E
New Contributor

I used to be able to enter a Date and Time combination into a single cell and it would return a valid date and time:  I would enter:  23 June 10:15 into a single cell and it would populate it with a correct date.time number.  The cell is custom formatted to d mmm hh:mm.  It no longer 

5 Replies

@TM415 

It still works for me. Have you changed your system date and time settings?

Does the problem persist if you quit and restart Excel? If you restart your computer?

Thanks for the reply.
My date/time settings on the computer are correct.
Yes, the problem is in the spreadsheet. When I enter the custom d mmm hh:mm date time it turns the entry to text format. The cell is still registered as "Custom" but the entry is now text.

@TM415 

The number format doesn't matter (as long as it isn't Text).

When you enter something in a cell, Excel asks the operating system whether it recognizes it as a date according to its date/time settings. Apparently, your system doesn't recognize '23 June 10:15'. On an English language system, it should recognize it as far as I know...

I discovered I can get Excel to recognize date/time entry as a valid combination if I type the hh:mm first, followed by d mmm yy. So, full syntax is: HH:MM D MMM YY works. If I do not try to include a time then any combination of day, month year seems to be recognized by Excel and a date number.

@TM415 

Nice to hear that you found something that works.