SOLVED

# The date keeps breaking my formulas.

Copper Contributor

# The date keeps breaking my formulas.

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?

4 Replies
best response confirmed by ShaneATV (Copper Contributor)
Solution

# Re: The date keeps breaking my formulas.

@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)``

# Re: The date keeps breaking my formulas.

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

# Re: The date keeps breaking my formulas.

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

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

# Re: The date keeps breaking my formulas.

Thank you all... Much appreciated :)
1 best response

Accepted Solutions
best response confirmed by ShaneATV (Copper Contributor)
Solution

# Re: The date keeps breaking my formulas.

@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)``