How to enter a date using period or dot (.) as the separator; and using yyyy.mm.dd as the entry format? My system preferences short date format is set up as yyyy.mm.dd. Numbers will recognize “-” and “/”. I do not care to use those for data entry.
Example: Typing 2021.9.2 into a date cell should be accepted as a date that I might format as 2021 Sep 2.
I do not usually format dates with dots, but it is my preferred method of data entry on a numeric key pad; and it is the way I have done it in Excel for years.
—–
Bob T
To let it accept a period as a separator in date entry, you'll need to change a setting in your system. Go to System Preferences, Language & Region. Then click the Advanced button at the bottom. Click Dates at the top. Then in the "Short" definition of the date, select the / characters and change them to a period.
Make sure the order is what you want too. For me it shows as Month / Day / Year so if you want Year / Month / Day you may have to change that.
Click OK.
Now in Numbers you should be able to enter things like 7.31.2021 and it will accept them as dates.
You may still need to change the date format so after entering 7.31.2021 to doesn't format it s something else.
Gary, thanks for replying.
As I recalled that Excel used the system date format, I checked my Mac system preferences. That is why I said "My system preferences short date format is set up as yyyy.mm.dd."
I suppose one might wonder why Numbers is willing to accept either "-" or "/" as the separator if only one style is allowed.
Bob: It worked for me when I tried it. Maybe try a restart? Keep playing with it.
I retyped the dots and rebooted — didn’t work.
Numbers seems to think “-“ is the separator of choice.
Typing w dots creates a Text field and changes the cell format — not nice.
Typing an invalid date 2021/02/29 changes the cell format — really not nice, because editing or retyping is not good enough.
Numbers help makes reference to “text strings that you type in a recognized date format” for data entry without stating what they are.
Dots are big in Europe.
Any guidance appreciated.
Bob: I tried it again and it worked for me. See my first comment. A screenshot is here: https://imgur.com/a/Gt22RHV
It shows the first date I entered normally. Then you can see I changed the / to dots in System Preferences. Then the second date I entered as 9.10.2021 and it accepted it. You can see the Cell formatting to the right.
I changed sys pref short date char to something silly — Accepted; Finder ok; reboot; Numbers does not recognize it.
I changed sys pref short date char back to dots — Accepted; Finder ok; reboot; Numbers does not recognize it.
I changed sys pref short date to Euro dd.mm.yyyy — Accepted; Finder ok; reboot; Numbers does not recognize it.
Clearly Numbers is not referencing sys pref short date for me. Also, automatic date styles never changed.
Thanks Gary for your effort — I’m sure you are right.
Bob: I'm at a loss as to why it isn't working for you, sorry. Works for me and I never had to reboot either.