Table for invoice number

[AcquaRegia]

Hello, i have a simple question to ask. I have a file in excel that i use to print the invoice for my work and also to store the data of the client and the link to the previous invoice.

In one of the table i wanted to have the invoice progressive number and in order to do so i used the concatenate function and i simply put the progressive of the month and the year. 

This was okay untill now because i made one invoice at month, but now i need to make three of them each month. Is there a more elegant solution that automatically generate the progressive number as shown without having to add the number manually? It also need to change the year after the last invoice of the current year.

I've attached a screenshot of the table right now. The formula that i'm using is the following (i take as example the cell B40)

=CONCATENATE(E40;".";$F$40)

Thank you in advance.

[#2]

@AcquaRegia 

As a 365 dynamic array

= LET(
  k,    SEQUENCE(108,1,0),
  m,    QUOTIENT(k,3),
  date, EDATE(start,m),
  seq,  1+MOD(k,3),
  TEXT(date,"mm.yy-")&seq )

seems to work.

[#3]

@Peter Bartholomew yeah, i think you'll need to guide me to use that ^^"

I'm quite skilled in the basics of excel with formulas and so on, but i don't know how to use VBA and other things

[#4]

@AcquaRegia 

I can see why you are confused, but the formula is just a standard worksheet formula in Excel 365!

LET is a new worksheet function that uses an alternating pattern of parameters to define names (variables) and the formulas that they refer to.  SEQUENCE is another function that I have used to generate an index column but in legacy Excel this would be done with ROW().

The key question is do you have access to Excel 365?  It is very different from anything that has gone before.

[#5]

I'm sorry for the delay in the response. Unfortunately i do not have acces to Excel 365, my copy of Excel comes from a suite of Office Professional Plus 2019.

[#6 · Accepted solution]

@AcquaRegia 

For example in row 1:

=TEXT(MOD(QUOTIENT(ROW()-1,3),12)+1,"00")&"."&QUOTIENT(ROW()-1,36)+18&"."&MOD(ROW()-1,3)+1

And then fill down as far as you want. This version will start with 01.18.1 for 2018. If you want to start in a different year, change 18 in the formula.

[#7]

Thanks, i couldn't use the one you wrote (i've tried to translate in italian but some function does not translate exactly), but with this i was able to make a new one that works perfectly. I've used

=TEXTJOIN(".",TRUE,TEXT(ROW()-3;"00"),RIGHT(B4,2))

So for example this is the one in the cell A4. In B4 i wrote the year as a four digit number and all i have to do is change the year whenever i print the last invoice of the year and change the -3 based on whatever the number of the row is the for the first invoice of the new year.

[#8]

@AcquaRegia 

Glad that you found a solution. The Italian version of the formula that I posted would be

=TESTO(RESTO(QUOZIENTE(RIF.RIGA()-1;3);12)+1;"00")&"."&QUOZIENTE(RIF.RIGA()-1;36)+18&"."&RESTO(RIF.RIGA()-1;3)+1