Converting String to Date and Calculating Date Lag - Crystal XI 1

[GayleC]

I have a string that contains a date in the format of "phs cey 070311 phsbord" I've written the following formula entitled "ManualDateConversion" to extract the date portion from the string:

Mid ({Batch.Name},9,6)

The formula returns 070311

I'm then using the following formula entitled "DateV2" to convert this date to Date format:

numbervar yr := val(right({@ManualDateConversion},2));
numbervar mt := val(left({@ManualDateConversion},2));
numbervar dy := val(mid({@ManualDateConversion},3,2));
Date (yr,mt,dy)

The formula returns 07/03/11.

I then use the following formula to calculate the differnce between the date retrieved above and another date entitled "@visitdate" (@Visitdate is a formula to convert a date time format to date only format):

{@DateV2}-{@visitdate}

If my @visitdate is equal to 07/06/11 the result of this formula should be "3" however it returns "-730785"

I've tried just adding 730488 to the formula (which is cheating), however when I then add a formula to exclude holidays and weekends, which works in other reports, the holidays begin to miscalculate. It appears that Crystal is not seeing my DateV2 as a true date. I've tried several other methods of converting the string to date including DateSerial but then I get a Bad Date Format error when calculating my Date Lag.

Any help would be appreciated.

 

[lbass]

numbervar yr := 2000+val(right(x,2));

You need to have four digits in the year place.

-LB

 

[GayleC]

Thank you! That did the trick and my Holidays are working correctly now as well.