Formatting String Function

[bld21]

I am using crystal 2008. I have in a formula the following:

right({@usps 3},10)

this returns a date of 1/26/2010 4

but because of the field I am referencing if there is no space after the year it picks up that next character.

Is there a way to just format this field as 1/26/2010?

Thanks,
Bruce D.

 

[MCuthill]

Hi Bruce,

Are you using the "Right" or "Left" function? From the example, I would assume that the date string is at the beginning of the field and not the end from how you refer to characters after the date.

In any case, assuming the above (that you are using Left):

If IsNumeric(Left({@usps 3},2)) THEN Left({@usps 3},10) ELSE Left({@usps 3},9)

This formula checks to see if the first 2 characters are numbers (month is 10, 11 or 12) then takes the appropriate number of characters based on the result.

12/26/2010456 returned 12/26/2010
1/26/2010456 returned 1/26/2010

Of course, I think this may need revision as you will have an issue with "Days" less than 10. I am certain one of the more clever folks here at TekTips may have a solution to your issue.

Hope this points you in the right direction!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[kray4660]

I think the easiest would be use the instr function to find the first space, then use the left function to the the date. Just a thought.