Date Format Question

C

carl

I have text vlaues that are like this:

Aug1
Aug10
Aug11
Aug14
Aug15
Aug16
Aug17
Aug18

Is there a way to convert these to actual dates ?


Thank you in advance.
 
T

Toppers

One way:

Set up table as shown below (I used columns L and M in the example formula):

Apr 4
Aug 8
Dec 12
Feb 2
Jan 1
Jul 7
Jun 6
Mar 3
May 5
Nov 11
Oct 10
Sep 9

and use the following (date in A1), with cell formatted as DATE:

=DATE(2006,LOOKUP(LEFT(A1,3),$L$1:$L$12,$M$1:$M$12),MID(A1,4,255))

Assumption is all months are 3 characters as defined the table above

HTH
 
Top