[Solved] Add custom date selection?

Discuss the spreadsheet application
Post Reply
CaliRay
Posts: 71
Joined: Sun Apr 26, 2009 6:20 pm

[Solved] Add custom date selection?

Post by CaliRay »

Is it possible to add a custom date format to the list of choices when formatting cells for a date?

The format I need is dd space month in 3 letter abbreviation space year like this: 01 Jan 2018
Last edited by Hagar Delest on Fri Jan 19, 2018 10:52 pm, edited 1 time in total.
Reason: tagged [Solved].
OOo 4.1.3 on Windows 10 Home
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Add custom date selection?

Post by Lupp »

A custom date format you actually used for a cell or during the creation of a named cell style should be appended to the list from which you can pick a number format of the 'Date' group anyway.

The format you described is represented by the code "DD MMM YYYY" in locales using the English code letters. In the pick list it will be shown as an example like "31 Dec 1999".

If this doesn't work for you, or doesn't cover your needs, please explain.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
CaliRay
Posts: 71
Joined: Sun Apr 26, 2009 6:20 pm

Re: Add custom date selection?

Post by CaliRay »

I've read the "Accessing and applying styles" page and your reply. After reading the explanation, I don't see how a style can be applied to a cell or range of cells when it doesn't already exist in the date drop down list.

Maybe the attached sample spreadsheet is better for showing what I'm hoping to accomplish. Cell A1 is a standard date format from the date drop down list. Cell B1 finds if the sum of the numbers of the date is equal to 1, 3, 5 or 7 as you helped me with before. Cell C1 is formatted as text and is the way I need the final result to be formatted.
Attachments
sample date conversion.ods
(8.69 KiB) Downloaded 93 times
OOo 4.1.3 on Windows 10 Home
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Add custom date selection?

Post by Lupp »

CaliRay wrote:I've read the "Accessing and applying styles" page...
I'm not sure where you found that page. It is not part of the offline help delivered with my AOO V4.1.5.
CaliRay wrote:...I don't see how a style can be applied to a cell or range of cells when it doesn't already exist in the date drop down list.
A number format code as I gave it in my first answer you can directly enter into the line 'Format code' of the 'Numbers' part of the cell formatting dialogue.
(A named cell style is applied by selecting it from the 'Styles an Formatting' dialogue, and is, of course, not selectable there as long as it is not defined.)
See also attachment.
Attachments
aoo92031NumberFormatDate_1_sample_date_conversion_Reworked.ods
(32.62 KiB) Downloaded 59 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
CaliRay
Posts: 71
Joined: Sun Apr 26, 2009 6:20 pm

Re: Add custom date selection?

Post by CaliRay »

The reference is https://wiki.openoffice.org/wiki/Docume ... ing_styles. It applies to Writer but don't know if it's for Writer only.
OOo 4.1.3 on Windows 10 Home
CaliRay
Posts: 71
Joined: Sun Apr 26, 2009 6:20 pm

Re: Add custom date selection?

Post by CaliRay »

I found this from 2010 basically saying that you can't create your own date format so I'm still looking for a solution. https://ubuntuforums.org/showthread.php?t=1357245
I'll try, as in the example below, to change the default but I have no idea how it will affect any other documents created in OO in the future. I don't know if the change would be document specific or global. At this point I don't have much to lose since I can probably fix any problems with a fresh install of OO.
Re: Change Default Date Format in OpenOffice Calc?
I finally figured out how to do this and am going through and posting the solution to the threads I've been reading for help:

1) in spreadsheet press Ctrl F11
2) Default - Modify
3) Date
4) change the language until you find one that defaults to the format you like (you CANNOT specify your own format) I like Uyghur and Zulu
5) click OK - OK
6) File - Templates - Save
7) File - Templates - Organize
8 ) right click the saved template and "Set As Default Template"

When a date is entered, it will now be recognized from the correct format and will be rendered in the correct format.
OOo 4.1.3 on Windows 10 Home
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Add custom date selection?

Post by Lupp »

CaliRay wrote:It applies to Writer but don't know if it's for Writer only.
The format codes are the same in 'Writer' and 'Calc', but the ways to apply them are different. In 'Writer' documents there are inserted fields e.g. and text-tables. Only the cells of inserted tables possess a context menu 'Number Format...' there, that looks similar to the 'Numbers' tab in Calc.
If you search for something concerning Calc, make sure to stick to Calc. Consider to read the guide (https://wiki.openoffice.org/w/images/b/ ... cGuide.pdf) which will help you to learn step by step. It is labeled with an older version, but there are not many differences.

The steps you list for Calc only apply if you want to create a template ... setting the number format for all the cells by changing the 'Default' cell style which either applies to all cells directly or gives heritage of its formats to most or all of the other styles existing in the document. First do it the simple way I demonstrated in my attachment above, then probably learn to create new styles based on 'Default', and finally feel informed well enough to use templates and heritage in styles.

Once again: Consider to start a phase of systematic learning. Always be aware of the fact that an Office suite is kind of a high-tech product and not just a toy. You surely wouldn't start to drive a car without any traioning, would you? "Press 'Start' and enjoy your computer" is just an advertising gag (lie) by MS.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Add custom date selection?

Post by Lupp »

Caliray wrote:When a date is entered, it will now be recognized from the correct format and will be rendered in the correct format.
The numbered steps from your previous post are somehow strange. Of course you can define additional number formats, among them date or currency formats, e.g. They will not get additional recognition patterns (See below!) by that. And there is nothing like a "correct format". There are just customs and settings, many of the customs bad.

You cannot define additional patterns making Calc interpret specific actually entered content (a sequence of keystrokes basically) as meaning something different, date to be handled as a number, e.g. The date recognition patterns and also patterns concerning currency amounts are defined via the locale and the country, concerning formats called "language" in short. It seems the author of the steps you listed did not thoroughly differentiate between formats (for output) and recognition/acceptance patterns (for input).

(LibreOffice allows to define additional date-input-patterns not provided by the language. The implementation is not perfect.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply