How do I enter dates in cells using dashes (DD-MM-YYYY) then have Calc change it to slashes (DD/MM/YYYY), or any other chosen format, as I do in Excel? If I have a cell date formatted for DD/MM/YYYY and enter 01-01-99 it does not change to 01/01/1999 or even 01/01/99.
Hello,
-
Understand what a date is (It is an integer number)
-
Input of data in general got nothing to do with the format of a cell (some exceptions exist from this rule)
-
Input of a date requires a conversion of what you enter into a number (see 1). This requires to follow a date acceptance pattern; i.e. which textual input will be recognized as a date and correctly converted into a number. This date acceptance pattern highly depends on your language settings (see
Tools -> Options -> Language Settings -> Languages -> Option: Date acceptance patterns). The only universal date acceptance pattern is:YYYY-MM-DD(ISO 8601). This pattern will always be recognized and correctly turned into a number -
Now and only now, formatting comes on to the scene. Formatting provides the presentation of that recognized numerical value. Formatting provides a rule how to show an the given integer value to the user of the application.
Having said this:
- Remove any content of a currently formatted cell
- Remove any format
- Enter
1999-01-01(or in any date format that matches your date acceptance pattern) - Now format the cell how you want to appear it.
Hope that helps.
In short, if you want 01-01-99 being accepted as DD-MM-YY date input and your locale setting does not include such pattern already then append this to date acceptance patterns ;D-M-Y (note the leading semicolon list separator).
I’ve been working with Excel for 20 years, happily entering dates as 1-1-99, then just watching them change to whatever date format I had the range of cells formatted to. Never once have I needed to alter date acceptance codes in Excel to get the ending date format how I wanted it. So for 3 hours I screwed with my Calc sheet & searched for existing answers but found none.
THANK YOU very much for your quick reply and very clear explanation—works like a charm!
Greg
Hello @goco
Be so kind to tick in the round mark aside the top left of this answer to acknowledge you agreed it.
Kind regards, Michel
this doesn’t work for me on 7.0.3.1
What doesn’t work? It does for me, I also just tried in a de-DE locale where appending the pattern then gives D.M.Y;D.M.;D-M-Y and input of 01-01-99 results in date 01.01.99 (01.01.1999)
Never once have I needed to alter date acceptance codes in Excel
Excel doesn’t know such thing, it tries to guess things with various separators and converts any input that vaguely resembles a date even if wrong in the current locale’s context and the user has no control over it.
@erAck - sorry, but I don’t know what made adding ;D-M-Y to date acceptance patterns on a fresh LibreOffice English (US)-profiled 7.0.3.1 version not accepting an input of 01-01-99 last time. Repeated again and now could not reproduce the failure which caused my comment above. So everything is correct as per your comment. Sorry again …