date acceptance patterns not sticking

users
#1

Libreoffice is driving me nuts.

I am in Canada. UI is set to English US; Locale settings to English Canada and Default Currency is CAD.

I have my Mac set dates to dd.mm.yyyy. (OSX 10.9.x)

For some reason Libreoffice (4.3.1.2) will only accept dates in the YYYY-MM-DD format.

I'm happy that I can format the date to look DD.MM.YYYY but entering in the YYYY-MM-DD is totally unnatural for me.

Actually, I only usually type the DD.MM and expect the YYYY to show up automatically in Excel. I'd like the same behaviour in LO if possible.

The date acceptance patterns will not accept anything except what is loaded by default ... which is
D.M.YYYY;DD-MM-YYYY;D-M

I don't want to have to type the YYYY each time for D.M.YYYY.

I've tried to add D.M to the acceptance pattern but the text turns red and won't save. The D.M is always gone after trying to save.

So if the date acceptance patterns are supposed to be customizable by the user ...

what do I have to do to get this to work?

Thanks in advance.
B.

PS I'm on digest mode. Please CC me directly

#2

Try this:

right click on a cell
select Format Cells
in category choose User Defined
in Format code enter: 00"."00".2014"

Then you can go to that cell and enter for example: 2512 and it should display as 25.12.2014.

A couple of things to remember. This looks like a date but I don't think you could do date math with it. I do this all the time but I just need it to visually look like a date. Also in 86 years you will need to rewrite it.

Regards, Jim

#3

Hi :slight_smile:
Is this set-up in somewhere weird like

Format - "Styles and Formatting"?

Maybe one of the early chapters in the Published Guides might be able to
help set it more permanently;
https://wiki.documentfoundation.org/Documentation/Publications
Ahh, chapter 3 of the "Getting Started Guide" is about styles.

Regards from
Tom :slight_smile:

#4

bunk3m wrote:

Libreoffice is driving me nuts.

I am in Canada. UI is set to English US; Locale settings to English
Canada and Default Currency is CAD.

I have my Mac set dates to dd.mm.yyyy. (OSX 10.9.x)

For some reason Libreoffice (4.3.1.2) will only accept dates in the
YYYY-MM-DD format.

I'm happy that I can format the date to look DD.MM.YYYY but entering in
the YYYY-MM-DD is totally unnatural for me.

Actually, I only usually type the DD.MM and expect the YYYY to show up
automatically in Excel. I'd like the same behaviour in LO if possible.

The date acceptance patterns will not accept anything except what is
loaded by default ... which is
D.M.YYYY;DD-MM-YYYY;D-M

I don't want to have to type the YYYY each time for D.M.YYYY.

I've tried to add D.M to the acceptance pattern but the text turns red
and won't save. The D.M is always gone after trying to save.

Works for me with LibreOffice 4.3.0 on Windows Vista; perhaps it's changed since then, or maybe it's different on Mac anyway. The problem with doing it, though, is that the date acceptance patterns affect what's interpreted as a date even for cells not formatted as date. That may be the reason for a change between 4.3.0 and 4.3.1.

With "D.M" added to the date acceptance patterns, entering "12.6" in a cell formatted as General becomes "12/06/14" - which may not be desirable if you actually wanted to enter a number between 12 and 13.

Even more bizarrely, explicitly formatting a cell as number (0.00) and then entering "12.6", it becomes "41802.00" (probably the numeric value for the 12th June 2014) But "43.6" becomes "43.60" as expected, presumably because it can't be interpreted as a valid date anyway.

Would using "/" or "-" as a separator be an acceptable alternative? So you could enter "12-6" or "12/6" to get the date 12th June 2014.

#5

bunk3m wrote:

Thanks so much for taking time to answer my question.

I never thought about the 12.6 issue messing up the decimal number
format as it never happened in Excel. As I've moved over to using LO,
I've learned some Excel behaviours really don't translate very well.

A bit more below.

bunk3m wrote:

Libreoffice is driving me nuts.

I am in Canada. UI is set to English US; Locale settings to English
Canada and Default Currency is CAD.

I have my Mac set dates to dd.mm.yyyy. (OSX 10.9.x)

For some reason Libreoffice (4.3.1.2) will only accept dates in the
YYYY-MM-DD format.

I'm happy that I can format the date to look DD.MM.YYYY but entering in
the YYYY-MM-DD is totally unnatural for me.

Actually, I only usually type the DD.MM and expect the YYYY to show up
automatically in Excel. I'd like the same behaviour in LO if possible.

The date acceptance patterns will not accept anything except what is
loaded by default ... which is
D.M.YYYY;DD-MM-YYYY;D-M

I don't want to have to type the YYYY each time for D.M.YYYY.

I've tried to add D.M to the acceptance pattern but the text turns red
and won't save. The D.M is always gone after trying to save.

Works for me with LibreOffice 4.3.0 on Windows Vista; perhaps it's
changed since then, or maybe it's different on Mac anyway. The problem
with doing it, though, is that the date acceptance patterns affect
what's interpreted as a date even for cells not formatted as date.
That may be the reason for a change between 4.3.0 and 4.3.1.

With "D.M" added to the date acceptance patterns, entering "12.6" in a
cell formatted as General becomes "12/06/14" - which may not be
desirable if you actually wanted to enter a number between 12 and 13.

Even more bizarrely, explicitly formatting a cell as number (0.00) and
then entering "12.6", it becomes "41802.00" (probably the numeric
value for the 12th June 2014) But "43.6" becomes "43.60" as expected,
presumably because it can't be interpreted as a valid date anyway.

Damn, never even considered this. This could have gotten really messy.

Would using "/" or "-" as a separator be an acceptable alternative? So
you could enter "12-6" or "12/6" to get the date 12th June 2014.

No it wouldn't be a problem. I had a brain cramp and couldn't get past
my old learned behaviour.

Thanks again for your help.

B.

Great. Thanks for letting us know this works for you. Hopefully it won't take too long to adjust ;o)

Mark.

#6

Libreoffice is driving me nuts.

I am in Canada. UI is set to English US; Locale settings to English
Canada and Default Currency is CAD.

I have my Mac set dates to dd.mm.yyyy. (OSX 10.9.x)

For some reason Libreoffice (4.3.1.2) will only accept dates in the
YYYY-MM-DD format.

I'm happy that I can format the date to look DD.MM.YYYY but entering in
the YYYY-MM-DD is totally unnatural for me.

Modify your default template for spreadsheets if you have any.
Add a German cell style for the dates.
Apply number format code TT.MM.JJJJ which means 2-digit Tag(day),
2-digit month and 4_digit Jahr (year)
Use that style for all cells where you want to use this input method:
25. => 25.11.2014 (this month's 25th day)
25.12 => 25.12.2014 (this year's 25th of December).
This can not work with any English locale because the point conflicts
with the decimal point.

Save this as your default template for your new spreadsheets:

Save... [some name]
Organize... pick your new spreadheet and choose "Set

Default" from the command button on the right.

Additionally you may define a custom shortcut to apply this style
(Tools>Customize>Keyboard, category "Styles".