Date format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arne Hegefors - 12 Sep 2007 09:18 GMT

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!

Reply to this Message

Stefi - 12 Sep 2007 12:06 GMT

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:

> 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!

Reply to this Message