Joined: 21 Feb 2012
Online Status: Offline
Posts: 28
Topic: String Date Conversion to Date Type Posted: 21 Feb 2012 at 12:58pm
I need help with my formula below. I am trying to format my string date field into a date format field using the Date() function.
However, my string date field is either 5 OR 6 digits long. First, I need to evaluate if it is 5 or 6 digits, so that it assigns the correct characters to the variables. (My dates are stored as MMDDYY or MDDYY.) Then, I need to evaluate the year to see if it should have a 19** or 20** prefix for the year.
The sticking point now is the last IF statement evaluating the "yyear" variable. The error message I receive says, "The remaining text does not appear to be part of the formula." Can someone point me in the right direction for this? Thank you! Any help is greatly appreciated!
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Posted: 21 Feb 2012 at 5:16pm
Part of the problem is the order of the params to the Date function should by (year, month, day) and you have (month, day, year). Also there's a problem with your formula if your dates are in "MMDDYY" or "MDDYY" format - you're using the first characters as day instead of month. Thirdly, you need to trap for non-numerics and make sure you have valid values. Try something like this instead:
Joined: 21 Feb 2012
Online Status: Offline
Posts: 28
Posted: 22 Feb 2012 at 9:26am
Thank you for your help and for catching my typo with the date/month! Whoops!
I tried your recommended code below and made sure the syntax was Crystal Syntax, but I still received the "The remaining text does not appear to be part of the formula." error for the part in red. I'm not sure if it matters but I am using CR xi release 2. Any idea what could be causing that? That is what I was getting before too. (Other than my typo of course. hehe)
Joined: 21 Feb 2012
Online Status: Offline
Posts: 28
Posted: 22 Feb 2012 at 9:45am
Disregard. I figured out what needed to happen. Just to document what I did for anyone else who may wonder, I had to put the red if statement from my reply post within each IF statement the length of the field as shown below.
**************CODE************
Numbervar yyear := 2000; Numbervar mmonth := 0; Numbervar dday := 0; If length({@DatePosted}) = 5 then ( If IsNumeric(left({@DatePosted}, 1)) then mmonth := ToNumber(left({@DatePosted}, 1)); If IsNumeric(mid({@DatePosted}, 2, 2)) then dday := ToNumber(mid({@DatePosted}, 2, 2)); If IsNumeric(right({@DatePosted}, 2)) then yyear := ToNumber(right({@DatePosted}, 2)); If mmonth > 0 and dday > 0 then if yyear < 50 then Date(yyear+2000, mmonth, dday) else Date(yyear+1900, mmonth, dday) ) else if length({@DatePosted}) = 6 then ( If IsNumeric(left({@DatePosted}, 2)) then mmonth := ToNumber(left({@DatePosted}, 2)); If IsNumeric(mid({@DatePosted}, 2, 2)) then dday := ToNumber(mid({@DatePosted}, 2, 2)); If IsNumeric(right({@DatePosted}, 2)) then yyear := ToNumber(right({@DatePosted}, 2)); If mmonth > 0 and dday > 0 then if yyear < 50 then Date(yyear+2000, mmonth, dday) else Date(yyear+1900, mmonth, dday) )
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
