Trying to format two string fields as a date

[dave_282]

Hi all,
I have two objects in a database that are both string fields, there are some text fields in them but the one I’m interested in are used as numbers to indicate a month and a year. Can I format those fields into a date field and display it as a MMYYYY value? Even in the case highlighted in yellow, where the number is now correct, it still showing as an error when trying to make it a date? Any thoughts anyone please?
If I can get this working as a usable date, we want to use it as an alert against currentdate.

[P User Cat1] is the year so im trying to add 20 to the start to make a full year and [P User Cat2] is the month.

Var1:
=ToNumber([P User Cat2])+“20”+ToNumber([P User Cat1])
Varr2:
=ToDate([Var1];“MMYYYY”)

[mgrackin]

Try the following formula. It will make your value a true date. You can then apply date formatting to the display in the table to format it as you wish. You will need to add some logic to this formula to deal with values that are not dates.

=ToDate(Substr([TheTextObject];4;2)+“20”+Substr([TheTextObject];1;2);“MMyyyy”)

[rogerperkins]

Crystal formula sample:

stringVar x := “17.10”;

x := “20” + x;

stringVar yyyy := Split (x,“.”)[1];
stringVar mm := Split (x,“.”)[2];

DateValue (ToNumber (yyyy), ToNumber (mm), 1)