0

Problem We have an Excel table where the column's category is date and the type has been set to "dd.m.yy"

enter image description here

Excel would recognise any new dates typed into the table of that format as dates.

At some point this stopped happening and now dates must be typed in, in the format "dd/mm/yy" at which point, Excel then formats them as "dd.mm.yy".

Why will Excel no longer recognise my date formats?

Set-up

  • Excel Version: Microsoft Office Professional Plus 2010
  • Windows: 10
  • File type: .xlsx
2
  • Are the cells you're trying to enter those dates in, formatted to the style you want? – Herb Sep 10 '17 at 14:44
  • @HerbWolfe Yes they are. – Dominic Woodman Sep 11 '17 at 9:16
1

There are two separate things at issue here. Formatting cells tells Excel how to display information once it has been entered. It does not affect how Excel interprets data you are entering into blank cells.

So if you type:

25.12.3

into a cell, Excel will make it a string. To get a date, you must type:

25/12/2003

if your locale is day/month/year.

It is easier to see what is happening by examining the Formula Bar.

1
  • Ah thanks for that! I presume it's not possible to change how Excel interprets dates? The things I've come across in my digging, don't seem to refer to anything as simple as date formats – Dominic Woodman Sep 11 '17 at 9:18
0

Excel uses Windows to know if you entered a date. It does so by comparing the pattern of what you entered to the pattern established in your (or your users') Windows settings.

For example, if your Windows settings were for the date format to be "dd.m.yy" and you enter "31.12.21", Excel would match that pattern of characters to the Windows setting's pattern and find it COULD be a date, and so it would record the entry as "December 31, 2021."

This is usually what someone wants. If all one's use on a given computer will need the above format, it works nicely. However, sometimes it is not by which I mean that they have a general Windows need for a different format (let's say "dd/mm/yyyy" for this purpose) and a particular EXCEL need for the above format. The particular need is usually for some format, needed for whatever reason, that is different from the general national format.

Sounds like you have that problem. There is one, and only one, way to achieve it outside of writing code (VBA, Javascript, etc.) in a macro that checks any entry in the relevant cell range for that exact pattern and manipulates it into a date (letting Excel handle it from there) or date value so there is no question of Excel mishandling it.

That would be to change your, and/or your users' Windows setting for dates to match this format. It seems unlikely you can, or even that you would want to, since that would mean all other programs on the computer would be affected.

The "take away" is this: only the date format Windows has will be innately recognized by Excel as a date entry. Nothing whatever about the formatting of the OUTPUT in Excel will affect how it interprets, and then handles, the INPUT.

There is no other way to have this exact keystroke entry pattern recognized innately by Excel as a date.

The workaround would be the "On Enter" kind of macro I mentioned which would let the entry be made, then immediately change it as needed and rewrite the cell. The precise entry would thus be replaced, so one might not like that, but the appearance of the result would be the above while the users still used the pattern they were familiar with (or prefer, whatever the reason is).

Nothing else can ACT upon an entry not considered a date by Excel, and then force Excel to do so. One could use, say, the helper column approach, or a separate data entry area where the entries are done as desired but not considered dates, and the separate output area where people work with them would formulaically tear apart the string and put it back together so Excel takes it as a date and then formats it to look like your desired format.

Why did this happen? As you say, at some point it just stopped working. My best bet is computers using the file had their Windows settings changed to accommodate this... long ago in the world of "someone else made that happen and did not pass that part on when you took it over." Then they started getting replaced and the IT workers doing the replacements had no idea a particular, non-standard, Windows date setting was required for some single use and put them in with the standard settings. (To be fair, those nimrods never transfer ANY settings at all, so the best you could have done is have the people involved make the change anyway... IT people were never going to take care of it.)

So, get all the Windows default date formatting changes acceptably, use a workaround (perhaps one of the two I mentioned), or live with it.

I do understand "live with it" might require re-writing "some" formulas to extract information from the entered dates differently than they now do. That might not be non-trivial. But if you can't make the changes mentioned and MUST live with it, you'll just have to do it. Likeliest ways it would cause trouble would be anything that used string operations on the raw date entries instead of using actual date functions on them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.