Convert text to date with a twist??

[Kallen]

Okay,

I have a field that is a text field but shows as a date ie DD/MM/YY. I need to convert this field to a date so I can group by the month.

Here is the twist:
There is also text some in some of the fields called -NA- instead of a day DD/MM/YY. I think this was used instead of null and it is driving me crazy because I can't think of a way around this.

Is there any formula I can do that will say something like

If {RenewalDate} = "-NA- then
Now //which would be the current month

any help would be greatly appreciated.

Thanks

 

[JasonLord]

if you are in 8.5 (8.0 probabalty too) you can use this formula

if isdate({RenewalDate})
then cdate({RenewalDate})
else now

 

[mbarron]

I would use the following formula:

If IsDate({Renewal.Date}) Then Date({Renewal.Date})
Else
CurrentDate

This way you'll get today's date whenever the renewal date is non-date text. (Just in case the "-NA-" wasn't entered as "N/A" or some other variation) Mike

 

[synapsevampire]

Try to do this on the database side, or use a SQL Expression. Datefields should NOT be stored as a string (char/varchar).

But your theory seems fine:

Create a formula:

If {RenewalDate} = "-NA- then
currentdate
else
date(val(mid({RenewalDate},7,2)),val(mid({RenewalDate},4,2)),val(left({RenewalDate},2)))

(or you can use this for the corrected date
date(mid({RenewalDate},7,2)+"/"+mid({RenewalDate},4,2)+"/"+left({RenewalDate},2))

I didn't test, but you get the idea.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com