Change the Excel date input format?

user_4790632 0
Viewed 1473

I've been struggling with Excel (2016) date formats. I know how to change display formats for dates and cells but the problem I have is the input format for dates. If I input a date as "DD.MM" or "DD.MM.YYYY" it does recognize it as a date but if I input the date as "DD.MM." (with the second dot after the month), Excel does not recognize it as a date anymore. The column in question is formatted as short date.

Is there anything that can be done or is this by-design? If so, it seems really strange as at least in my country it's the official way to write the date containing that second dot after the month number when there is no year included in the date.

I've been searching and Googling for solution but couldn't find anything on this really. I appreciate all comments and help regarding this question!

SUMMARY/TL;DR:

  • Excel version is 2016, country is Finland and language is finnish
  • Excel accepts/recognizes these as dates: 12.5 or 30.8
  • Excel does NOT accept/recognize these as dates: 12.5. or 30.8.
  • The column in question is formatted as short date
  • The dot after the month seems to be screwing things up
  • Why is this happening? Can anything be done?

Kind regards, Tenttu

excel date
1 Answers

Yes, it is/was by design. (Funny enough, my Excel won't allow dots, only dashes (-), so I couldn't even test if "15.8" works)

So, there's a slight chance that the language of Excel (the defaults of time (24 hours or AM/PM), dates (MM/DD or DD/MM), decimals (comma or dot) etc.) wouldn't allow the dot at after the month. Here's an example of a user that has that dot, and wants to get rid of it. So, your system language is a good candidate for why this wouldn't work for you.

However, I realize that the example linked above don't feature a date with a dot at the end. Which could suggest it is rather by design. For example, if I add a dot to a valid date or time, it will result in some #VALUE!-error. And that's because of how Excel is programmed to convert text to a date - and remember, dates are actually just really large numbers. So, adding a dot at the end makes that conversion "impossible". We might think it's as easy as to remove a dot, but in programming, we need to program that explicitly to do that, and I'm leaning towards there is no such operation done during text to date conversion (certainly not on my system, as I get #VALUE!).

One work-around is to strip the ending dot from the date to make it a valid date. So, you can import sheets with dates with dots at the end, then strip them away, and you'll be good to go!

user_54245350
Related