Solved

convert UK vehicle registration numbers into year of registration

Posted on 2011-03-03
7
432 Views
Last Modified: 2012-05-11
Hi guys,

Has anyone got any code that will determine year of registration from UK license plates?

eg...
LD10AAA = 2010
AB60AAA = 2010
LD11BBB = 2011
CD55BBB = 2005
X123ABC = 2000
X12ABC = 2000
X1ABC = 2000
M123ABC = 1996
.....


I'm aware that code won't be right in all cases and that personalised plates obfuscate year of reg.

Many thanks
Jon



0
Comment
Question by:jondanger
7 Comments
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
You would need I think to construct a look-up table with the various codes/types of code in one column and the year in the second column. I lost track of number plates once they got rid of the letter at the end so am not currently au fait.
0
 

Author Comment

by:jondanger
Comment Utility
lol yes fair play... i could figure it out the donkey way.

I'm hoping someone else has done this and is willing to share.
0
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
Do you know what the rules are - from your list there doesn't appear to be much logic, but presumably there is?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
Comment Utility
Hello Jon,

Isn't the last one 1994?

Here's a formula approach, assuming that letter codes from 1983 to 2001 are shown in table codes

=IF(ISERR(MID(A2,2,1)+0),MOD(MID(A2,3,2),50)+2000,VLOOKUP(LEFT(A2),Codes,2,0))

or without a table you can use this longer version

=IF(ISERR(MID(A2,2,1)+0),MOD(MID(A2,3,2),50)+2000,IF(LEFT(A2)="U",17,IF(LEFT(A2)="X",18,MATCH(LEFT(A2),{"A","B","C","D","E","F","G","H","J","K","L","M","N","P","R","S","T","W","Y"},0)))+1982)

see both versions in the attached

regards, barry
26860856barry.xls
0
 
LVL 6

Accepted Solution

by:
akajohn earned 250 total points
Comment Utility
Hi
Got a slightly different version from barry.
I used http://en.wikipedia.org/wiki/Vehicle_registration_plates_of_the_United_Kingdom
I got slightly different results and only checked for the codes up to 1983 approx.

Hope this helps.
A>
UKLicensePlateLookup.xls
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
Prefix letter year codes UK - see below...
Ref:

http://en.wikipedia.org/wiki/Vehicle_registration_plates_of_the_United_Kingdom#Year_identifiers
Letter 	Dates of issue
A 	1 August 1983 – 31 July 1984
B 	1 August 1984 – 31 July 1985
C 	1 August 1985 – 31 July 1986
D 	1 August 1986 – 31 July 1987
E 	1 August 1987 – 31 July 1988
F 	1 August 1988 – 31 July 1989
G 	1 August 1989 – 31 July 1990
H 	1 August 1990 – 31 July 1991
J 	1 August 1991 – 31 July 1992
K 	1 August 1992 – 31 July 1993
L 	1 August 1993 – 31 July 1994
M 	1 August 1994 – 31 July 1995
N 	1 August 1995 – 31 July 1996
P 	1 August 1996 – 31 July 1997
R 	1 August 1997 – 31 July 1998
S 	1 August 1998 – 28 February 1999
T 	1 March 1999 – 31 August 1999
V 	1 September 1999 – 29 February 2000
W 	1 March 2000 – 31 August 2000
X 	1 September 2000 – 28 February 2001
Y 	1 March 2001 – 31 August 2001

Open in new window

0
 

Author Comment

by:jondanger
Comment Utility
thanks guys, both precise and elegant!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now