Re: [libreoffice-users] need some help with custom number format code

2018-03-19 19:33 keltezéssel, Brian Barker írta:
> 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
> ="AB-"&TEXT(A1;"MM/YYYY")
> 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:
> ="AB-"&TEXT(DATE(2018;ROW();1);"MM/YYYY")
> 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.
>
> Brian Barker
Thank you very much for this short tutorial! These codes are for 
numbering documents. The first  part - in this case AB or AB- refer to 
the type of the course these documents belong to. 01 etc are serial 
numbers from 01 to -say- 60. the last part /2018 refers to the current 
year and should not be incremented.

Regards, Albert


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
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