Convert text to date

Hi, I'm translating the formula from a Italian Excel. I hope it could do its job.

=DATE(RIGHT(A1,4)+0,VLOOKUP(LEFT(A1,3),{"Jan",1\"Feb",2\"Mar",3\"Apr",4\"May",5\"Jun",6\"Jul",7\"Aug",8\"Sep",9\"Oct",10\"Nov",11\"Dec",12},2,0),MID(A1,5,2)+0)

Regards

I couldn't understand exactly what you were trying to do Canapone, but I'm afraid it doesn't work in my version of Excel.

Matt, try:
=DATEVALUE(MID(A1,5,3)&LEFT(A1,4)&RIGHT(A1,4))

NB we prefer here if thread starters acknowledge replies (ideally reflecting the time someone else spent helping them)

Hi, translating from Italian Excel I've inverted the slash with the coma in the VLOOKUP segment.

This is my second try, I hope to have more luck

=DATE(RIGHT(A1,4)+0,VLOOKUP(LEFT(A1,3),{"Jan"\1,"Feb"\2,"Mar"\3,"Apr"\4,"May"\5,"Jun"\6,"Jul"\7,"Aug"\8,"Sep"\9,"Oct"\10,"Nov"\11,"Dec"\12},2,0),MID(A1,5,2)+0)

I was trying a very simple

=Date(year,month,day)

I had no problem to extract and make it read years and days to DATE formula.

To translate Jan/Feb/Mar … into numbers I wrote a table of correspondences (Jan = 1; Feb = 2; ….) in G1:H12 and I’ve used a VLOOKUP


VLOOKUP(LEFT(A1,3),G1:H12,2,0) as second argument of DATE

I've highlighted the second argument of VLOOKUP G1:H12 from the formula bar , press F9 to translate/explicit (sorry: I don't know the correct expression to describe this use of command F9) my table into the formula in order to unplug VLOOKUP from reading G1:H12.

Regards

In English Excel:
=DATE(RIGHT(A1,4),VLOOKUP(LEFT(A1,3),{"Jan",1;"Feb",2;"Mar",3;"Apr",4;"May",5;"Jun",6;"Jul",7;"Aug ",8;"Sep",9;"Oct",10;"Nov",11;"Dec",12},2,0),MID(A1,5,2))

NB you don't have to coerce the arguments of date (i.e. the +0 bit) - date will coerce numbers entered as text.

I think I prefer the way datevalue can turn a string into a date without having to use any kind of lookup - that said, if you are going to use a lookup this is perhaps a bit neater:

=MATCH(A1,{"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},0)

I don't envy you learning Excel whilst having to deal with transliteration issues.

FWIW, another slightly shorter alternative for getting a month number from a mmm string is to use:

Please Login or Register  to view this content.

where A1 holds "Jan" etc...