GTIN Calculation is basically of different types like GTIN 8, GTIN 12, GTIN 13 and GTIN 14. We can calculate GTIN by using Excel Formula as well as Excel Macro.

GTIN Calculator By Formula | GTIN Calculator By Macro | How to Calculate GTIN | Download GTIN Calculator

GTIN Calculation- By Excel Formula:

GTIN-8"000000"&F11 & (IF(MOD(3*(MID(F11,1,1) + MID(F11,3,1) + MID(F11,5,1) + MID(F11,7,1))+(MID(F11,2,1) + MID(F11,4,1) + MID(F11,6,1)),10)=0,0,((QUOTIENT(3*(MID(F11,1,1) + MID(F11,3,1) + MID(F11,5,1) + MID(F11,7,1))+(MID(F11,2,1) + MID(F11,4,1) + MID(F11,6,1)),10)+1)*10)-(3*(MID(F11,1,1) + MID(F11,3,1) + MID(F11,5,1) + MID(F11,7,1))+(MID(F11,2,1) + MID(F11,4,1) + MID(F11,6,1)))))
GTIN-12"00"&F12 & (IF(MOD(3*(MID(F12,1,1) + MID(F12,3,1) + MID(F12,5,1) + MID(F12,7,1) + MID(F12,9,1) + MID(F12,11,1))+(MID(F12,2,1) + MID(F12,4,1) + MID(F12,6,1) + MID(F12,8,1) + MID(F12,10,1)),10)=0,0,((QUOTIENT(3*(MID(F12,1,1) + MID(F12,3,1) + MID(F12,5,1) + MID(F12,7,1) + MID(F12,9,1) + MID(F12,11,1))+(MID(F12,2,1) + MID(F12,4,1) + MID(F12,6,1) + MID(F12,8,1) + MID(F12,10,1)),10)+1)*10)-(3*(MID(F12,1,1) + MID(F12,3,1) + MID(F12,5,1) + MID(F12,7,1) + MID(F12,9,1) + MID(F12,11,1))+(MID(F12,2,1) + MID(F12,4,1) + MID(F12,6,1) + MID(F12,8,1) + MID(F12,10,1)))))
GTIN-13"0"&F13 & (IF(MOD((MID(F13,1,1) + MID(F13,3,1) + MID(F13,5,1) + MID(F13,7,1) + MID(F13,9,1) + MID(F13,11,1))+3*(MID(F13,2,1) + MID(F13,4,1) + MID(F13,6,1) + MID(F13,8,1) + MID(F13,10,1) + MID(F13,12,1)),10)=0,0,((QUOTIENT((MID(F13,1,1) + MID(F13,3,1) + MID(F13,5,1) + MID(F13,7,1) + MID(F13,9,1) + MID(F13,11,1))+3*(MID(F13,2,1) + MID(F13,4,1) + MID(F13,6,1) + MID(F13,8,1) + MID(F13,10,1) + MID(F13,12,1)),10)+1)*10)-((MID(F13,1,1) + MID(F13,3,1) + MID(F13,5,1) + MID(F13,7,1) + MID(F13,9,1) + MID(F13,11,1))+3*(MID(F13,2,1) + MID(F13,4,1) + MID(F13,6,1) + MID(F13,8,1) + MID(F13,10,1) + MID(F13,12,1)))))
GTIN-14F14 & (IF(MOD((MID(F14,1,1) + MID(F14,3,1) + MID(F14,5,1) + MID(F14,7,1) + MID(F14,9,1) + MID(F14,11,1)+ MID(F14,13,1))+3*(MID(F14,2,1) + MID(F14,4,1) + MID(F14,6,1) + MID(F14,8,1) + MID(F14,10,1) + MID(F14,12,1)),10)=0,0,((QUOTIENT((MID(F14,1,1) + MID(F14,3,1) + MID(F14,5,1) + MID(F14,7,1) + MID(F14,9,1) + MID(F14,11,1)+ MID(F14,13,1))+3*(MID(F14,2,1) + MID(F14,4,1) + MID(F14,6,1) + MID(F14,8,1) + MID(F14,10,1)+ MID(F14,12,1)),10)+1)*10)-((MID(F14,1,1)+ MID(F14,3,1) + MID(F14,5,1) + MID(F14,7,1) + MID(F14,9,1)+ MID(F14,11,1) + MID(F14,13,1))+3*(MID(F14,2,1)+ MID(F14,4,1)+ MID(F14,6,1)+ MID(F14,8,1)+ MID(F14,10,1)+ MID(F14,12,1)))))

VBA Code to Calculate GTIN

Sub gtin_cal(normal)

Dim n
Dim n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12

n1 = CInt(Mid(normal, 1, 1))
n2 = CInt(Mid(normal, 2, 1))
n3 = CInt(Mid(normal, 3, 1))
n4 = CInt(Mid(normal, 4, 1))
n5 = CInt(Mid(normal, 5, 1))
n6 = CInt(Mid(normal, 6, 1))
n7 = CInt(Mid(normal, 7, 1))
n8 = CInt(Mid(normal, 8, 1))
n9 = CInt(Mid(normal, 9, 1))
n10 = CInt(Mid(normal, 10, 1))
n11 = CInt(Mid(normal, 11, 1))
n12 = CInt(Mid(normal, 12, 1))

n = (n1 + n3 + n5 + n7 + n9 + n11) + (n2 + n4 + n6 + n8 + n10 + n12) * 3

q = Int(n / 10)

r = n Mod 10
If (r <> 0) Then
append_value = (10 * (q + 1)) - n
Else
append_value = 0
End If
gtin_val = "0" & normal & CStr(append_value)

End Sub

How to Calculate GTIN

Key Format

Digit positions

GTIN-8N1N2N3N4N5N6N7N8
GTIN-12N1N2N3N4N5N6N7N8N9N10N11N12
GTIN-13N1N2N3N4N5N6N7N8N9N10N11N12N13
GTIN-14N1N2N3N4N5N6N7N8N9N10N11N12N13N14
Step 1: Multiply value of each position by 1 0 3 as mentioned below for each of the GTIN
31313131313131313
Step 2: Add results together to create sum
Step 3: Subtract the sum from nearest equal or higher multiple of ten = Check Digit
Step 4: Now Append the Check Digit at the End of the Number. Also we need to make the complete Number of 14 Digit, So append the remaining digits as Zero as a Prefix.

Download this GTIN Calculator

I have created one Excel Workbook which can calculate all types of GTIN. If you want all GTIN calculator in Excel, download by clicking on the below download button.

GTIN Calculator
Download Now

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!