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,
1) Understand what a date is (It is an integer number)
2) Input of data in general got nothing to do with the format of a cell (some exceptions exist from this rule)
3) 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
4) 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:
1999-01-01 (or in any date format that matches your date acceptance pattern)Hope that helps.
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
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 ...
Asked: 2020-11-17 19:12:33 +0100
Seen: 60 times
Last updated: Nov 17 '20
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
