My company uses an order numbering system like the one in this example.
F21260001
. Breaking it down, the “F” is the month of June (6th letter of the alphabet)
The next 4 numbers are the day of the month and the last two digits of the year.
The last four numbers are the actual order entry number.
Each order is one line on the spreadsheet.
Currently, we create the complete order number by hand, but someone at the office said there must be a way for the order number to be generated automatically. We have started using Libre Calc about two weeks ago, but everything from the past 40 years has been done on either a database or Excel. How do we do this? No one in the office has experience creating spreadsheets; we just use what the boss has provided.
And where do you encounter any differences between Calc and Excel? I don’t get it.
In any spreadsheet application
=DATE(VALUE(MID(A1;4;2))+2000;CODE(LEFT(A1;1))-64;VALUE(MID(A1;2;2))) yields 2026-06-21
=RIGHT(A1;4) gives “0001”
OQ wasn’t about the analysis, but about the composition.
perhaps
[A] order NUMBER
[$B$1] order date
[C] CHAR(MONTH($B$1) + 64)
[D] DAY($B$1)
[E] RIGHT(YEAR($B$1);2)
In practice, just one concatenation with all formulas, of course.
Or, using most of @CRDF work you could add automatic numbering when the date is entered. It does need the Numbering column but it could be hidden
CustomOrderNumber135816EA.ods (17.0 KB)
Of course a database would be better, the data could be reused for quoting, follow-up, invoicing, etc. At the same time a simple sequential order number system would already be available.
=BASE(MONTH(B1)+9;22) & TEXT(B1;"ddyy") & TEXT(A1;"0000")
Hi @EarnestAl, suggestion without the extra column:
In cell B2
=IF(ISBLANK(A2);"";CONCATENATE(CHAR(MONTH($A2) + 64);TEXT(DAY($A2);"00");RIGHT(YEAR($A2);2);TEXT(ROW()-1;"0000")))
I don’t get it either. I am a writer, speaker, and dispatcher, not a programmer or spreadsheet guru. That is why I came here for help. The format of the order number is what he cares about; hence, my question about how to achieve that format.
Now, looking at EarnestAI’s work and subsequent discussion, I can present the boss with a workable solution. The only addition I can seek as a dispatcher would be a two-letter code after the initial order number for scheduled (SH) and round-trip (RT) orders. I mention this because if I add anything to the generated order number, it breaks the order generation sequence.
Add another column C for the codes “SH” or “RT”.
Afterwards you can add a data validation for only these 2 options of text.
So, @EarnestAl’s formula at D2 becomes
=IF(ISBLANK(A2);"";CONCATENATE(CHAR(MONTH(A2) + 64);TEXT(DAY(A2);"00");RIGHT(YEAR(A2);2);TEXT(B2;"0000");C2))
etc…
As the actual numbering is limited to 9999 I assumed that the numbering must be reset every day but maybe it is reset every month, or every year. Or is it supposed to roll over back to 1 after it reaches 9999?
Or every day from 1 for each SH and RT options
If so, it could be 2 sets of columns, each starting at 1 at new date…
In this case, the numbering rolls over (resets each day). The SH orders start each day at 0001SH and increase to around 50 (depending on the day of the week). The normal workflow orders start after the scheduled orders. So if we have 22 SH (scheduled orders), the first regular order for today would be F23260023.
I don’t get it @CRDF. I copied and pasted your suggestion. The results give the order number for row 2 for every row. C2 never increments the way the original formatting code did; with @EarnestAl’s code, the B column still increments as it should.
Just add a column C to @EarnestAl solution.
In it you insert “SH” or “RT”, and the formula adds (concatenate) it at the end.
You are saying that this is none of your business. Why do you care?
Something like this could e a more “professional” solution…
Columns DE are auxiliary for storage (persistence) and can be hidden.
Option Explicit
Sub SetOrderRef(Evt As Object)
Dim Doc As Object, SForm As Object, Form As Object, DatePicker As Object, CData As Object, CNumero As Object, COption As Object
Dim order As String, sData As String
Dim data As Date
Dim order_number As Integer
On Error GoTo Erro
REM SET VARIABLES FOR THE CONTROLS:
Doc = ThisComponent
SForm = Doc.CurrentController.ActiveSheet
Form = SForm.DrawPage.Forms.getByIndex(0)
DatePicker = Form.getByName("dData")
CData = SForm.getCellByPosition(3, 0) ' cell D1 - persistência da data em curso
CNumero = SForm.getCellByPosition(3, 1) ' D2 - persistência do último n° usado
COption = SForm.getCellByPosition(3, 2) ' D3 - option selected in radio buttons
REM SET VALUES:
sData = DatePicker.Text ' date of the order
data = CDate(sData)
REM SET ORDER N°:
order_number = CNumero.Value + 1 ' same date increments order n° by 1
If data > CData.Value Then order_number = 1 ' if date changed order n° resets to 1
CNumero.Value = order_number ' persist last order n°
CData.Value = data ' persist current date
REM BUILD ORDER NUMBERING STRING:
order = Chr(CInt(Mid(sData, 4, 2)) + 64) & Left(sData, 2) & Right(sData, 2) & CStr(order_number) & COption.String
StoreOrderRef(Doc, order)
Exit Sub
Erro: MsgBox "ERRO " & Err & Chr(10) & "na linha " & Erl
End Sub
REM ROUTINE TO STORE ORDER # INTO LAST CELL OF SHEET Orders
Sub StoreOrderRef(Doc As Object, order As String)
Dim SOrders As Object, NewCell As Object, Cursor As Object
Dim lr As Integer
On Error GoTo Erro
REM SET VARIABLES FOR THE OBJECTS:
SOrders = Doc.Sheets.getByName("Orders")
Cursor = SOrders.createCursor()
REM FIND LAST USED ROW:
Cursor.gotoEndOfUsedArea(False)
lr = Cursor.RangeAddress.EndRow
REM GET THE NEW CELL AND INSERT ORDER #:
NewCell = SOrders.getCellByPosition(0, lr + 1) ' Column A - new_cell
NewCell.String = order
Doc.CurrentController.setActiveSheet(SOrders)
Doc.CurrentController.Select(NewCell)
Exit Sub
Erro: MsgBox "ERRO " & Err & Chr(10) & "na linha " & Erl
End Sub
Fire the boss. I know a lot of confusing “systems” to create designators for orders or invoices. This one is a candidate for for the “medal for the worst idea”.
disask_135816_WinnerOfTheCompetitionForTheWorstIdeaToCreateOrderNumbers.ods (9.9 KB)
.
But actually it is only an american date MonthDayYear, where somebody saved one position space and used A,B,C instead of 01,02,03 …
A still missing statement as far as I noticed:
The creation of order numbers based on dates requires that the date values or their components are entered as literals.
Based on a TODAY() or a NOW() formula they would be recalculated and by that changed on occasions.
To get them as literals based on such functions would require to permit the execution of user code, and that is
- a risk concerning intruders and unforeseen mishaps.
- not feasible for Calc AND Excel in one document without lots of problems.