LinkBack URL
About LinkBacks
Basically, i want to work out the age of a large list of vehicles based on its uk registration plate number but have so far been completely out of luck, anyone got any ideas?
Cheers in advance
Chris Jackson
Basically, i want to work out the age of a large list of vehicles based on its uk registration plate number but have so far been completely out of luck, anyone got any ideas?
Cheers in advance
Chris Jackson
Well, as I know nothing of Uk Registration Plate Numbers, I assume that there is either A) A Master list with the date of the registration somewhere or B) embedded in the registration number (i.e. 20051125AAA would be November 25th 2005).
Perhaps you can give us some more information?
Thanks,
John
Yeah.
To accomplish this, we would either need to know the algorithm that translates the Plate # to a create date, or have a master list that we could lookup values in.
If either of these are true, I can't believe the resulting formulas would be hard to impliment.
`engineers08
This may be a starting point. It depends how you want to treat the date of registration, as it can vary by a full year (or 6 months for the newer registrations). Hopefully this is a start.
Let me know how you get on
Regards
Dav
Thanks for the help, what i wanted to do was to be able to put the reg number in say PF07 KFG which would be 6 months old and it would tell me that, this is fine but for the older plates, there was a different format for example X234 ABC would be September 2000 which would be 7 years, the problem i have is that several of my busses are new **57 *** reg (around 1 month old) however on the first of January they will show up as being 1 year old when in fact they are just over 2 months old. is there any way i can use an if function to find out if the second digit is a letter, if it is than use an algorithm for the old style plate or if not an algorithm for the new style plate, if someone could point me in the right direction regarding that it should get me on my way and would be greatly appreciated.
Chris
That's what Dav's suggestion does. This part of the formulaOriginally Posted by chris2403
ISNUMBER(MID(A3,2,1)*1)
returns TRUE if the second character is a number, FALSE if not
Ah, thats good, didn't get round to looking at that zip above.
Thanks, will let you know how i get on.
Dav,
May be worth looking here, only some registration are from:-
January-December
August-July
August-February
March-August
September-February
So not that straight forward?
http://www.checkthatcar.com/registration%20plate1.asp
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
So Far, i have broken it down so i have these formulas:
=IF(ISNUMBER(MID(D45,2,1)*1), (MID(D45, 1, 1)),(MID(D45, 3, 2)))
This Returns the registration. For S123 ABC, it will return S, for AB55 ABC, it will return 55.
=VLOOKUP(G45,$E$51:$F$86,2, TRUE)
This this looks up the value the above formula calculates and looks it up in a table. This is where it falls apart, i get an #N/A error whenever the Registration is of the newer format eg AB55 ABC, if its is the older format the function works perfectly and if i type the value in manually it works, the table is as such (i plan on changing the values in F to return the month eg march and including a second lookup to calculate the year and then getting the age from this):
E F
A 1983
B 1984
C 1985
D 1986
E 1987
F 1988
G 1989
H 1990
J 1991
K 1992
L 1993
M 1994
N 1995
P 1996
R 1997
S 1998
T 1999
V 1999
W 2000
X 2000
Y 2001
2 2002
3 2003
4 2004
5 2005
6 2006
7 2007
8 2008
51 2001
52 2002
53 2003
54 2004
55 2005
56 2006
57 2007
58 2008
Last edited by chris2403; 10-16-2007 at 10:03 AM.
try the lookup with a false and not a true. It will then find an exact match and should work.
the values in F can be entered as dates with a month and a year, so no need for 2 lookups. However the question for calcualting age is what month to use as it can vary by a full year in some instances. The result can then be taken away from Today() to get the age of the vehicle.
does that work?
Regards
Dav
Last edited by Dav; 10-16-2007 at 10:08 AM.
Sorted, Used this:
=IF(LEN(G45)=1, (VLOOKUP(G45,$E$51:$F$86,2)), (VLOOKUP(--G45,$E$51:$F$86,2, TRUE)))
Instead of:
=VLOOKUP(G45,$E$51:$F$86,2, TRUE)
=VLOOKUP(G45,$E$51:$F$86,2, false)
was all you needed, the other way works but is just longer
=IF(ISNUMBER(MID(D45,2,1)*1), (MID(D45, 1, 1)),(MID(D45, 3, 2)*1))
Would change the text to a number
Regards
Dav
Last edited by Dav; 10-16-2007 at 10:32 AM.
Tried that but it didnt work. All sorted now, Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks