View original (French)

Structured Communication

Solved
Griseba Posted messages 124 Registration date   Status Member Last intervention   -  
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   -
Hello,

I want to obtain a structured communication in the following format: +++012/3456/789..+++
with the date =TODAY() that is in H13 and the invoice number in H14 that I enter manually.

What formula should I enter to get this structured communication with the slashes and the plus signs?

Thank you for your help.

Configuration: Windows / Firefox 94.0

8 answers

Answer 1 / 8
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
 
Hello,

+++171/1202/1025+++ means: 17 of the 11th month in 2021, invoice 025 + the 2 verification digits

Your request makes no logical sense to separate the month, for example 11, into two 1/1 and how do you do it for the first nine months of the year from 1 to 9 as well as the year 2021 into 202/1
when the logic would be ++++17/11/2021/025 +++etc ...

now if you insist on your logic 
="+++"&TEXTE(JOUR(H13);"00")&GAUCHE(TEXTE(MOIS(H13);"00");1)&"/"&DROITE(TEXTE(MOIS(H13);"00");1)&GAUCHE(ANNEE(H13);3)&"/"&DROITE(ANNEE(H13);1)&H14&"+++"

A+
Mike-31

I am responsible for what I say, not for what you understand...
1
Griseba Posted messages 124 Registration date   Status Member Last intervention   2
 
It's because I don't have much choice: for it to work at the bank, I need to have 3 digits before the first /, 4 digits in the middle, and 3 digits + the last two control digits after the second /.

Example: +++123/1234/12345+++
In my case: 171/1202/125XX+++ 17 from the 11 in 2021; 25 representing the invoice number.

The structure imposed by the Belgian banking standard is as follows:

+++ 000 / 0000 / 000XX +++

The last two numbers "XX" being control numbers.
0
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147 > Griseba Posted messages 124 Registration date   Status Member Last intervention  
 
Re,

are the two control numbers that you represent by XX fixed like the series of +++ or are these numbers in a cell,

if they are fixed you just need to include them in the formula before the last +++
and if they are in a cell include "your cell"+++.
0
Griseba Posted messages 124 Registration date   Status Member Last intervention   2 > Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention  
 
Only the +++ never change, it's the last two digits that change (control number) depending on the date and the invoice number.

To summarize, I understand that I need a cell with the date, a cell with the invoice number, and a cell that gives me the structured communication like the example below.

But it's the formula to obtain this example that I need.

+++000/0000/000XX+++

Thank you for your help.
0
Griseba Posted messages 124 Registration date   Status Member Last intervention   2 > Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention  
 
There are only the +++ that never change; it is the last two digits that change (control number) according to the date and the invoice number.

In summary, I understand that I need a cell with the date, a cell with the invoice number, and a cell that gives me the structured communication as in the example below.

But it's the formula to obtain this example that I need.

+++000/0000/000XX+++

Thank you for your help.
0
Griseba Posted messages 124 Registration date   Status Member Last intervention   2 > Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention  
 
there are only the +++ that never change, it's the last two digits that change (control number) depending on the date and the invoice number.

In summary, I understand that I need a cell with the date, a cell with the invoice number, and a cell that gives me the structured communication as in the example below.

But it's the formula to obtain this example that I need.

+++000/0000/000XX+++

Thank you for your help.
0
Answer 2 / 8
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
 
Re,

To add your control number, either it is fixed and you add it in the formula as below instead of Your number
="+++"&TEXT(DAY(H13),"00")&LEFT(TEXT(MONTH(H13),"00"),1)"/"&RIGHT(TEXT(MONTH(H13),"00"),1)&LEFT(YEAR(H13),3)"/"&RIGHT(YEAR(H13),1)&H14& "Your number""+++"

or it is in a cell, for example in cell H1 and you reference that cell
="+++"&TEXT(DAY(H13),"00")&LEFT(TEXT(MONTH(H13),"00"),1)"/"&RIGHT(TEXT(MONTH(H13),"00"),1)&LEFT(YEAR(H13),3)"/"&RIGHT(YEAR(H13),1)&H14&H1&"+++"
--
A+
Mike-31

I am responsible for what I say, not for what you understand...
1
Answer 3 / 8
DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717
 
Hello everyone, 

+++012/3456/789..+++


It's not very clear! Where's your date?
Give a concrete example, or better yet, an example file.

Best regards
0
Griseba Posted messages 124 Registration date   Status Member Last intervention   2
 
Hello DjiDji59430,

My structured communication would read as follows +++171/1202/1025+++ which means: 17 of the 11th in 2021, invoice 025 + the 2 verification digits.
0
Answer 4 / 8
Anonymous user
 
Hello,
I find it difficult to discern the structure of this communication. Could you explain more?
Best regards.
0
Griseba Posted messages 124 Registration date   Status Member Last intervention   2
 
Hello,

it is only the +++ that never change, these are the last two digits that change (control number) depending on the date and the invoice number.

To summarize, I understand that I need a cell with the date, a cell with the invoice number, and a cell that gives me the structured communication as in the example below.

+++000/0000/00000+++

But it's the formula to obtain this example that I need.

Thank you for your help.
0
Answer 5 / 8
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
 
Hello
do you put slashes in the middle of the numbers of the date?
If not:
to be adapted: with a date in A1, invoice number in B1
formula:
="+++"&TEXT(A1;"dd/mm/yyyy")&"/"&B1&"+++"
otherwise come back with more info
best regards

0
Griseba Posted messages 124 Registration date   Status Member Last intervention   2
 
There are only the +++ that never change; it's the last two digits that change (control number) according to the date and the invoice number.

To summarize, I understand that I need a cell for the date, a cell for the invoice number, and a cell that provides me with the structured communication as in the example below.

But it's the formula to obtain this example that I need.

+++000/0000/000XX+++

Thank you for your help.
0
Griseba Posted messages 124 Registration date   Status Member Last intervention   2
 
There are only the +++ that never change, it is the last two digits that change (control number) according to the date and the invoice number.

In summary, I understand that I need a cell with the date, a cell with the invoice number, and a cell that gives me the structured communication as per the example below.

But it is the formula to obtain this example that I need.

+++000/0000/000XX+++

Thank you for your help.
0
Answer 6 / 8
DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717
 
Hello everyone,

A trial:
https://www.cjoint.com/c/KKrsb5lG6tY

Best regards
0
brucine Posted messages 24691 Registration date   Status Member Last intervention   4 149
 
Good evening,

Mike has already proposed a functional solution. Since I don't like anything that's simple, I created my own using a larger number of LEFT, RIGHT and zeros instead of "TEXT", with this latter usage requiring testing whether the day or month dates are less than 10.

In short, once it was noted, also by Mike, that the syntax defies usual logic, there are a multitude of solutions...
0
Answer 7 / 8
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
 
Hello,

In addition to everything that has been said:
=TODAY() located in H13
Today being just yesterday of tomorrow, it will change every day upon opening...
Either enter the date manually in H13 or consider using VBA.
Eric

--
By continually trying, we eventually succeed.
So the more it fails, the more chances we have that it will work. (the Shadoks)
In addition to saying thank you (yes, it does happen!!!), remember to mark it as solved. Thank you.
0
brucine Posted messages 24691 Registration date   Status Member Last intervention   4 149
 
Hello,

Of course, I thought about that too.

But if this is meant to be an invoice template, I had done something like this in the past; there is nothing preventing you from freezing the date when you save your new invoice based on this template.
0
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281 > brucine Posted messages 24691 Registration date   Status Member Last intervention  
 
Of course, but in any case the formula =TODAY() is useless.
It's just as well to hard-code the date using the shortcut Ctrl+; or to retrieve it if VBA
eric
0
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281 > eriiic Posted messages 24581 Registration date   Status Contributor Last intervention  
 
Anyway, it's not of any interest to the applicant at all...
0
Griseba Posted messages 124 Registration date   Status Member Last intervention   2 > eriiic Posted messages 24581 Registration date   Status Contributor Last intervention  
 
I found it interesting, but it was convenient that the formula =TODAY() was suitable because it corresponds to the billing date.
0
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281 > Griseba Posted messages 124 Registration date   Status Member Last intervention  
 
Well, you will check your invoice number tomorrow.
0
Answer 8 / 8
Griseba Posted messages 124 Registration date   Status Member Last intervention   2
 
Mike-31, thank you for all your information, it works and finally makes the time I spend on my invoicing easier.

Best regards.
0