Date format

[Guest]

Hi! I have a program that exports data to Excel. The problem is that the
dates are in a format that is not recoginzed by Excel as dates. They look
like: 1997-maj-08, 1997-sep-24, 2005-jan-10 and so on. Is there any way to
covert these dates into normal Excel dates? If you know of a solution in VBA
instead of a worksheet function that would fine too! Thanks alot!

 

[Guest]

First make sure that month names appearing in exported dates (maj, sep, jan)
match short month names in your Excel national language version! If yes, then
this formula will do the job:
=DATE(LEFT(A1,4),MONTH(DATEVALUE(MID(A1,6,6))),RIGHT(A1,2))
If not then you have to replace month names like this (it's an example for
Hungarian language version: maj is replaced by máj, sep is replaced by sze)
=DATEVALUE(MID(SUBSTITUTE(SUBSTITUTE(A1,"maj","máj"),"sep","sze"),6,6))

Regards,
Stefi


„Arne Hegefors†ezt írta: