At 15:06 19/03/2018 +0100, Oszkó Albert wrote:
I would like to create a custom number format
code in Calc, to spare some work. This would be
like this AB-01/2018. I would like to increment
only the 01 part by dragging that little
rectangle on the lower right of the cell. So I
created as custom formats "AB-"##"/2018" or
"AB-"\/YYYY. Unfortunately in either case only
the year number is incremented and not what I wanted.
I'm not sure that's true - but it depends anyway
on the actual values you propose to enter into
the cells. Are you suggesting entering the number
1 and filling down to produce 2, 3, and so on -
or to enter the date 1 January 2018 (in whatever
format) and filling that down to 1 February 2018
and so on? In addition, is the 2018 part fixed,
or do you want that eventually to change? Do you
want 12/2018 to be followed by 13/2018, that is, or by 01/2019?
Your first form works if the cells contain just
the numbers 1, 2, and so on - not dates - though
it would have to use "00" in place of "##" if
January were to appear as "01" and not as "1" and
so on. And it runs out if you want 2019 to appear when appropriate.
How to proceed?
You could easily assemble this using a formula by
concatenating the text with a formatted version
of the relevant date. For example, put 1 January
2018 (as a date) in A1. If you fill down from
this, you will get 2 January and so on, which is
not what you want. Instead enter 1 February 2018
in A2. Now select A1 and A2 together and drag the
fill handle: that will give you 1 March, 1 April, and so on. Now in B1 enter
and fill that down the column. You can hide the
unwanted column A or put it away on another sheet if you prefer.
You could avoid the extra column by generating the date in the formula:
Fill that down to achieve the desired effect. If
your values start in a row other than the first,
you will need to change "ROW()" to "ROW()-x",
where "x" is some appropriate value. Note that
this formula relies on the ability of the DATE()
function to interpret the thirteenth month of 2018 as January 2019 and so on.
But yes: you can achieve this by formatting
dates. Just use the format "AB-"MM/YYYY. Once
again, you will need to enter your first two
dates (1 January and 1 February 2018) into the
first two cells, select both, and then drag the
fill handle so as to create dates one month
apart, not one day apart, as would happen if you
try to fill from a single prototype.
I trust this helps.
To unsubscribe e-mail to: firstname.lastname@example.org
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy