Excel Weigand Formula

Proximity cards are often encoded with Weigand formats. When importing to various access control software you need to understand how to format the data. This example is for the Alarm Lock’s 36 bit cards into their DL-Windows software.

 Format: NAP36bit
 Facility Code: 19
 Sequence: 831450     
 Weigand Binary:     
 OCCCCCCCCCCCCCCCCC CCCCCCCCCFFFFFFFFE
 123456789012345678 901234567890123456
 100000011001010111 111011010000100110

 Hexdecimal: 0008195FB426

 O (Odd Parity)
 =BITXOR(MOD(LEN(SUBSTITUTE(S5,"0","")),2),1)

 CCCCCCCCCCCCCCCCC CCCCCCCCCFFFFFFFF (Card and FC)
 ="0"&DEC2BIN(BITRSHIFT(M5,17),8)&DEC2BIN(BITAND(255,BITRSHIFT(M5,9)),8)&DEC2BIN(BITAND(511,M5),9)&DEC2BIN(L5,8)

 E (Even Parity)
 =MOD(LEN(S5)-LEN(SUBSTITUTE(S5,"0","")),2)

 Concatenate
 =R5&S5&T5

 HEX
 =BIN2HEX(LEFT(U5,8),2)&BIN2HEX(MID(U5,9,8),2)&BIN2HEX(MID(U5,17,8),2)&BIN2HEX(MID(U5,25,8),2)&BIN2HEX(MID(U5,33,4),1)

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *