Converting Date String to Date 2

[RobMcP]

Hi there,

I am trying to convert a date string (YYYYMMDD) to a date (DDMMYY). Using the following formula, I have met with partial success, the day and the year pull through correctly for each entry but the month is always (01).

E.g. (20011031 = 31/01/2001) or (20011201 = 01/01/2001) or (20031208 = 08/01/2003)

My formula is as follows.

//Convert The Status Changed Date

NumberVar xy := tonumber(Left({AUT001.Date Status Last Changed},4));
NumberVar xd := Day(Datevalue (tonumber(Right({AUT001.Date Status Last Changed},2))+1));
NumberVar xm := Month(Datevalue (tonumber(Mid({AUT001.Date Status Last Changed},5,2))+1));

Date(xy,xm,xd)

I am using Crystal 10 and I’m totally stumped.

Any help would be very much appreciated, thanks in advance.

 

[GJParker]

You were nearly there, just over egged the pudding slightly

//Convert The Status Changed Date

NumberVar xy := tonumber(Left({AUT001.Date Status Last Changed},4));
NumberVar xd := tonumber(Right({AUT001.Date Status Last Changed},2));
NumberVar xm := tonumber(Mid({AUT001.Date Status Last Changed},5,2));

Date(xy,xm,xd)

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[MisterMo]

Try

NumberVar xy := tonumber(Left(({AUT001.Date Status Last Changed},4));
NumberVar xd := tonumber(Right(({AUT001.Date Status Last Changed},2));
NumberVar xm := tonumber(Mid(({AUT001.Date Status Last Changed},5,2));

Date(xy,xm,xd)

Mo

 

[MisterMo]

Sorry Gary I should have checked before posting

Mo

 

[RobMcP]

Cheers guys that worked perfectly.

Rob

 

[GJParker]

Glad you got it sorted and thanks for the star.

Gary Parker
MIS Data Analyst
Manchester, England