SOLVED

unable to format date

%3CLINGO-SUB%20id%3D%22lingo-sub-2271024%22%20slang%3D%22en-US%22%3Eunable%20to%20format%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2271024%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20read%20half%20dozen%20plus%20of%20posts%20regarding%20date%20format.%26nbsp%3B%20None%20have%20resolved%20issue.%3C%2FP%3E%3CP%3EI'm%20working%20with%20an%20inherited%20spread%20sheet%20-%20fixing%2Fupdating%20drop%20down%20menus%20in%20other%20columns.%20Thought%20I%20was%20done%20but%20then%20tested%20date%20column.%26nbsp%3B%20When%20I%20key%20in%2041321%20I'm%20getting%2002%2F16%2F2013%20as%20an%20example.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20unmerged%20heading%20cells%20so%20I%20can%20select%20column.%20I%20tried%20the%20text%20to%20columns%2C%20selecting%20format%20cells%2C%20various%20date%20option%2C%20tried%20custom.%26nbsp%3B%20Per%20one%20post%20added%20a%20column%20to%20the%20right%20of%20existing%20but%20problem%20carried%20over%20to%20new%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(sheet%204%20has%20dropdown%20field%20contents)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFeel%20like%20I'm%20missing%20something%20simple.%3C%2FP%3E%3CP%3EOther%20ideas%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2271024%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2271435%22%20slang%3D%22en-US%22%3ERe%3A%20unable%20to%20format%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2271435%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1024917%22%20target%3D%22_blank%22%3E%40JoAStro%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20there%20is%20more%20then%20one%20date%20column.%3C%2FP%3E%3CP%3ESecond%2C%20I%20didn't%20understand%20what%20your%20problem%20is.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have read half dozen plus of posts regarding date format.  None have resolved issue.

I'm working with an inherited spread sheet - fixing/updating drop down menus in other columns. Thought I was done but then tested date column.  When I key in 41321 I'm getting 02/16/2013 as an example. 

 

I've unmerged heading cells so I can select column. I tried the text to columns, selecting format cells, various date option, tried custom.  Per one post added a column to the right of existing but problem carried over to new column. 

 

(sheet 4 has dropdown field contents)

 

Feel like I'm missing something simple.

Other ideas? 

 

Thought my info would auto populate  Windows 10 Ed / MS Office 365

9 Replies

@JoAStro 

First, there is more then one date column.

Second, I didn't understand what your problem is.

 

You need to enter it as "4/13/21". Excel dates are actually integers - the number of days from a base date (usually 1/1/1900). So, 41321 is 41,321 days from 1/1/1900, which translates to 2/16/13. The integer that represents today, 4/13/21, is 44299 (if you key that integer, you will get what you're expecting).

If you key the "/" symbols, Excel will know what you mean and put in the proper date.
Yes, column E and L are needed date columns. Column F was added as one of the posted suggestions was to add a new column to avoid date format issues.
The dates displayed are not the dates keyed in. When I key in digits for 4/12/21 date it populates as 02/16/2013. That's my problem. Digits keyed in are changed to others so dates area not accurate.
Yes, I've heard about the day count and figured that was probably what was populating.

I work with so many date fields in the software I use for work that do auto fill correctly am I just imagining that it can happen in Excel? I would have sworn that some Excel fields work for keying in the digits?
best response confirmed by JoAStro (Occasional Contributor)
Solution
No, excel doesn't have a date "field" and automatically add the "/" separators, but a date "format". The cell contents are an integer. So, keying 41321 is a valid date, which is 2/16/13. If you want to key dates as integers, then you have to use the integer for today's date, which is 44299 (or use the shortcut Ctrl+;).

If you pre-format the cells as text, then you can key the dates as you are doing now and then use text to columns at the end to convert them to what you want. The reason why text to columns is not currently working as you expect is the data is not text (note the tool is *text* to columns).

Or, you would have to use a helper column w/ a formula to convert your integers to the expected date values, or vba.
When I key in 4/12/21 I get 04.12.2021 in column E, 12.04.2021 in column F and 12.4.21 in column L.
Seems to me that it works correctly.
Yes, it does work if you key in "4/13/21". I truly thought one could key in "41321" to a field formatted for date and it would fill as 4/13/21. Apparently I was mistaken and it only happens in my software.
I'm afraid I don't follow what you mean by 'my software.' Is this another copy of excel on your work computer or a completely different software program?
Completely different software programs I use at my employment. Sorry if unclear - I had referenced in earlier post. Should have been clearer. Thanks for your replies.