editing a reference number to reflect date raised.

M

mcmillad

I have a load of reference numbers here, and am trying to find the the
dates they were raised.
example: OF05230050. This was raised on the 23/05/05 (The F represents
05). How can I get excel to recognise this and enter in a cell
23/05/05?
Is there a formula that can be entered to reflect this or can I use the
"text to column" function to split the reference number and stick it
back together in another order? (I've tried this already, but excel
does not recognise it as a date format. It needs to be this as I am
using a further formula to calculate the number of days it has been
open by minusing it from a now() formula). There are a lot of these
reference numbers so would like there to be as little manual work as
possible
 
A

Ardus Petus

Say your ref. is in A1.
=DATE(CODE(MID(A1,2,1))-65+2000,MID(A1,3,2),MID(A1,5,2))

HTH
 
Top