Re: [libreoffice-users] need some help with custom number format code
At 20:12 19/03/2018 +0100, Albert Oszkó wrote: Thank you very much for this short tutorial! No probs! 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. Aha: it's always best to define your problem accurately! In that case, surely your original format suggestion of "AB-"##"/2018" works, with just the numbers 1, 2, and so on as the cell contents. If - as you indicated - you want single digit numbers to have a preceding zero, change this to "AB-"00"/2018" . Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
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-"\/. 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-"(A1;"MM/") 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-"(DATE(2018;ROW();1);"MM/") 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/. 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+unsubscr...@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
Re: [libreoffice-users] need some help with custom number format code
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-"\/. 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-"(A1;"MM/") 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-"(DATE(2018;ROW();1);"MM/") 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/. 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 -- To unsubscribe e-mail to: users+unsubscr...@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
[libreoffice-users] need some help with custom number format code
Hi all, 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-"\/. Unfortunately in either case only the year number is incremented and not what I wanted. How to proceed? I use LO 5.4.1.2.0 on openSuse Tumbleweed with KDE. Thanks, Albert -- To unsubscribe e-mail to: users+unsubscr...@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
