Convert Text to Date

[shellas]

Is there a way to convert data in a text field to date? I have a text field with the data 2009-10, which should read October 2009. How can I convert this data to a date field (Oct 2009)?

Thanks.

[Fenton]

October 2009 is not a "date", so it cannot be converted to a date. 10/1/2009 is a date however. And a real date can be shown on the layout any way you want; but it will only work in Finds and relationships if you remember it is a date.

But that is only appropriate if you do not care what day of the month; it would not work if you were using it to match to a "year & month"; in that case you'd want: 200910

So, we don't really how to answer for what you may need. But it will be using Text Functions, possibly in combination with the Date ( month; day; year ) function (month in that case being a number).

[Derek1]

Thanks for information)

[Fenton]

OK, I was a bit lazy, as I didn't want to write out the calculation until there was more information. But this will convert 2009-10 to Oct 2009. Assuming 2009-10 is in the YearMonth_txt field:

Let ( [

yr_mo = Substitute ( YearMonth_txt; "-"; " " ); // split it so Words functions work

yr = LeftWords ( yr_mo; 1);

mo_num = RightWords ( yr_mo; 1 )

];

Choose ( mo_num ; ""; "Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec" ) & " " & yr

)

// Choose ( test; result1; result2 ) is a zero based function (about the only one), so in this case nothing for the 1st result for 0

[TheTominator]

Assuming 2009-10 is in the YearMonth_txt field:

Let ( [

yr_mo = Substitute ( YearMonth_txt; "-"; " " ); // split it so Words functions work

yr = LeftWords ( yr_mo; 1);

mo_num = RightWords ( yr_mo; 1 )

];

Choose ( mo_num ; ""; "Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec" ) & " " & yr

)

If you want the field's date settings on the layout to control the way the date is expressed you can use the following variation of Fenton's calculation to get a FileMaker date value. FileMaker can then localize the date display to the user's language. You can also use the value in date comparisons, math on dates, etc.

It will assume the date is the first of the month. As long as you have the date settings for the field's display to display only the month and year and you keep in mind that the day number is artificially introduced, then it won't be an issue.

Let ( [

yr_mo = Substitute ( YearMonth_txt; "-"; " " ); // split it so Words functions work

yr = LeftWords ( yr_mo; 1);

mo_num = RightWords ( yr_mo; 1 )

];

Date(mo_num; 1; yr)

)