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-14 F14 & (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-8 N1 N2 N3 N4 N5 N6 N7 N8
GTIN-12 N1 N2 N3 N4 N5 N6 N7 N8 N9 N10 N11 N12
GTIN-13 N1 N2 N3 N4 N5 N6 N7 N8 N9 N10 N11 N12 N13
GTIN-14 N1 N2 N3 N4 N5 N6 N7 N8 N9 N10 N11 N12 N13 N14
Step 1: Multiply value of each position by 1 0 3 as mentioned below for each of the GTIN
3 1 3 1 3 1 3 1 3 1 3 1 3 1 3 1 3
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!