The date keeps breaking my formulas.

[ShaneATV]

I have a formula to make a unique unidentifiable code for each person, but the date column keeps breaking the formula and doing weird things.

It's a basic formula =RIGHT(I2,2)&RIGHT(K2,4)&RIGHT(L2,2)&LEFT(N2,1)

It's designed to take the initials, postcode, year of birth (YY) and gender (Single letter) but the date keeps throwing out the formula. It should just take the last to numbers in the DOB column but does something random instead. In the example below the code should be AC420784F not AC420793F.

Anyone know how to fix this?

[#2 · Accepted solution]

@ShaneATV Dates in Excel are actually stored as sequential numbers, where 1 = 01-Jan-1900, 2 = 02-Jan-1900, etc. The date you have entered in cell L2 (12-Jan-1984) is equivalent to the number 30693 in Excel, which is why RIGHT(L2,2) returns 93. Try using the TEXT function for that cell to return the 2-digit year formatted as "YY":

=RIGHT(I2,2)&RIGHT(K2,4)&TEXT(L2,"YY")&LEFT(N2,1)

[#3]

@ShaneATV 

Instead of RIGHT(L2,2) use TEXT(L2,"yy").  In L2 is actually number which is only looks like date due to formatting.

[#4]

@ShaneATV You need TEXT() function to retrieve last two digit or year.

=RIGHT(I2,2)&RIGHT(K2,4)&TEXT(L2,"YY")&LEFT(N2,1)

[#5]

Thank you all... Much appreciated 🙂