Ask Your Question
0

Calc Date formats: 01-01-99 vs 01/01/99?

asked 2020-11-17 19:12:33 +0200

goco gravatar image

updated 2020-11-17 19:18:56 +0200

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
3

answered 2020-11-17 19:36:45 +0200

Opaque gravatar image

updated 2020-11-17 19:56:04 +0200

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:

  • 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.

edit flag offensive delete link more

Comments

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).

erAck gravatar imageerAck ( 2020-11-17 19:53:45 +0200 )edit

@erAck ... just to mention that this doesn't work for me on 7.0.3.1

Opaque gravatar imageOpaque ( 2020-11-17 20:00:50 +0200 )edit

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

goco gravatar imagegoco ( 2020-11-17 20:16:16 +0200 )edit

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

mgl gravatar imagemgl ( 2020-11-17 23:30:14 +0200 )edit

@Opaque

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)

erAck gravatar imageerAck ( 2020-11-20 14:05:05 +0200 )edit

@goco

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 gravatar imageerAck ( 2020-11-20 14:10:29 +0200 )edit

@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 ...

Opaque gravatar imageOpaque ( 2020-11-20 14:20:10 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-11-17 19:12:33 +0200

Seen: 72 times

Last updated: Nov 17 '20