Référence créancier - 98-modulo 97

Question

J'ai un "logiciel" de facturation maison sur Excel. Pour l'introduction de la facture QR en Suisse, je suis en train de voir comment je peux créer la référence créancier structurée (ou référence du créancier RF). Ce qu'il me manque c'est de pourvoir générer le code de vérification à 2 chiffres qui suit RF. Voici ce que j'ai trouvé sur le net : Constructing an RF Creditor Reference and calculating check digits The check digits consist of two numeric characters. The check digits are calculated for a reference by following the below steps: Convert upper and lower case letters to digits according to the following conversion table: A = 10 G = 16 M = 22 S = 28 Y = 34 B = 11 H = 17 N = 23 T = 29 Z = 35 C = 12 I = 18 O = 24 U = 30 D = 13 J = 19 P = 25 V = 31 E = 14 K = 20 Q = 26 W = 32 F = 15 L = 21 R = 27 X = 33 Add the numeric value of the character RF, 2715, and ”00” to the right-hand end of the creditor reference; Calculate modulo 97, i.e. the remainder of the division of the numeric character string “cccc...271500” by 97; Subtract the remainder from 98 and, if the result is less than 10, insert a leading zero. The result is the two check digits. In order to correctly represent the complete RF Creditor Reference on an invoice, the code “RF” and the check digits should be inserted at the left-hand end of the creditor reference. Examples of how to calculate the check digits Example 1: A creditor reference is 2348236. The method to determine the check digits is the following: Add to the right-hand end “2715” and “00”- the number becomes 2348236271500; After dividing by 97, the remainder is 65; Subtract the remainder from 98 - the result is 33. The letters “RF” and the check digits “33” are inserted at the left-hand end of the creditor reference and the resulting RF Creditor Reference is RF332348236 (electronic format) or RF33 2348 236 (print format). Je souhaite convertir mes références de factures en référence de créancier structurée : Mes références de factures sont conçues de la façon suivante : 2009271234 (AA -année-MM-mois-JJ-jour-HH-heure-MM-minute). Selon un générateur en ligne je devrai arriver au résultat suivant : RF342009271234 Est-ce qu'il y a un moyen de faire cela sur excel soit pas formule soit en vba ? Je remercie par avance toute personne qui pourra m'apporter son aide. Sandra...

X Cellus · Answer

En retour les formules pour générer le code de vérification et créer la référence créancier structurée. Selon exemple 1 et format de facture personnelle (AAMMJJHH) Bons tests, bonne continuation...

X Cellus · Answer

Le modulo (MOD) d'Excel n'apprécie pas les trop grands nombres. Donc une étape intermédiaire permet de les réduire. Ainsi dans l'exemple 1 la référence facture n'ayant que 7 chiffres. Il suffit d'appliquer une puissance (^3) pour abaisser le nombre. Idem pour le 2ième nombre mais celui-ci étant encore plus grand on utilise alors une puissance plus importante (^4). Ainsi on peut appliquer le modulo par 97 sur des nombres plus petit et il ressort le résultat escompté. Bonne continuation...

X Cellus · Answer

A nouveau,

Dans le cas de la facturation (AAMMJJHH) ce sera toujours ^4 qu'il faudra appliquer. Car la longueur de la référence est de 10 chiffres, donc ajouté avec 271500 en fin, le nombre passe et reste toujours à 16 chiffres (jusqu'en l'an 9999).

Ensuite c'est en effet un "0" de tête qu'il faut placer et non un "0" final comme inscrit avec la multiplication par 10. Il faut changer la condition IF par

IF(C14-C15<10;"0" & C15-C14;C14-C15)

La formule intermédiaire introduite pour montrer le détail peut être incluse avec l'autre modulo finale. Ce qui donne

=MOD(MOD(A15;97^4);97)

Cela donnera directement 64 dans l'exemple personnelle de facturation.

X Cellus · Answer

Suite,

Voilà la formule combinée:

=SI(98-MOD(MOD((A1*1000000+271500);97^4);97)<10;"RF0" & 98- MOD(MOD((A1*1000000+271500);97^4);97) & A1;"RF" & 98-MOD(MOD((A1*1000000+271500);97^4);97) & A1)

Pour la référence en A1.

X Cellus · Answer

A nouveau,

Oui, car dans le 2ième bloc juste après "RF0" car la soustraction de 98 et du résultat des MOD sera toujours positive.

Le 3ième bloc n'aura besoin que du RF car le check sera supérieur à 9

X Cellus · Answer

Suite,

J'ai modifié ma formule de11h58 au vu que le modulo par 97 serait supérieur au chiffre 98.

Après tests ce ne sera jamais le cas. Donc le digit check ne sera jamais négatif. Il faut laisser les deux blocs identiques, à part "RF0" dans le 2ième.

Je remets la dernière formule dans sa 1ière mouture.

X Cellus · Answer

Suite,

J'ai fait selon l'exemple 1. Je supposais par analogie au carte bancaire que ce serait 4 chiffres de séparation et si moins en final on laisserait ainsi.

Donc cela donnerait RF35 2009 2613 22 pour la facturation (AAMMJJHH). Soit 3 blocs de 4 et un bloc de 2.

En fait 2 possibilités, passer la formule STXT comme présentée ou créer un format personnalisé pour la cellule A2 ou une colonne particulière.

Pour cela passer par le Menu Acceuil puis Format d'Excel puis format personnalisé. En le créant de toute pièce 2 caractères texte puis 2 caractères numériques et 2 blocs de 4 chiffres puis un bloc de 2 chiffres séparés par des espaces.

Je vais le tester en fin de journée. Ou reposte si trouvé.

X Cellus · Answer

A nouveau,

A partir du format électronique FR342009271234 calculé en B18 (voir l'image fourni en 1ier message). Ou tout autre emplacement

Et du code ci-dessous intégré dans l'éditeur VBA. Faire ALT F11 puis insertion d'un module et y copier le code.

Function Chk(Nb as String)
For C= 1 to 12 step 4
Mot=Mid(Nb, C,4) & " "
Chk= Chk & Mot
Next C
Chk = Chk & Right(Nb,2)
End Function

Il suffit de taper en cellule B19, ici pour l'exemple

=Chk(B18) pour obtenir la version imprimable FR34 2009 2712 34

Bons tests, bonne continuation.

A = 10	G = 16	M = 22	S = 28	Y = 34
B = 11	H = 17	N = 23	T = 29	Z = 35
C = 12	I = 18	O = 24	U = 30
D = 13	J = 19	P = 25	V = 31
E = 14	K = 20	Q = 26	W = 32
F = 15	L = 21	R = 27	X = 33