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.  