Need help with TextToDate Please

[kiwioz]

Hi to all.

Please can someone help me with this?

I have 3-dropdown popup lists for Date of Birth.

1) Day 1 - 31

2) Month January, February, March and so on

3) Year 1910 - 2003

Now what I need to be able to do is convert this into a date format in another field.

Please Help

Thanks in advance.

Craig

[BertBoye]

The following calculation will do the trick for you.

TextToDate(Case(month="january", "1", month="february", "2", month="february", "2", month="march", "3", month="april", "4", month="may", "5", month="June", "6", month="july", "7", month="august", "8", month="september", "9", month="october", "10", month="fnovember", "11", month="december", "12") &"-"&day&"-"&year)

Hope it helps you.

[kiwioz]

Thank you very much.

I have one small problem.

I live in Australia and I need the date different i.e.

Day Month Year

I tried this but no luck

dob_day&"-"&TextToDate(Case(dob_month="january", "1", dob_month="february", "2", dob_month="february", "2", dob_month="march", "3", dob_month="april", "4", dob_month="may", "5", dob_month="June", "6", dob_month="july", "7", dob_month="august", "8", dob_month="september", "9", dob_month="october", "10", dob_month="fnovember", "11", dob_month="december", "12") &"-"&dob_year)

[kiwioz]

Just After I posted this I was reading once more then i saw it this works

TextToDate(dob_day&"-"&(Case(dob_month="january", "1", dob_month="february", "2", dob_month="february", "2", dob_month="march", "3", dob_month="april", "4", dob_month="may", "5", dob_month="June", "6", dob_month="july", "7", dob_month="august", "8", dob_month="september", "9", dob_month="october", "10", dob_month="fnovember", "11", dob_month="december", "12") &"-"&dob_year))

Thank you so much for the Help

I have one moer question please

I am needing to import records that have a date format like this 25_12_1968. How can I convert this to my 3 fields?

1. dob_day

2. dob_month

3. dob_year

Craig

[Lee Smith]

Hi Craig,

This calculation should work. Just replace "YourField" with the field you are using for the import.

Date(Middle(YourField , 4, 2), Left(YourField, 2), Middle(YourField, 7, 4))

Hope this helps

Lee

[kiwioz]

Thank you for your time but here it comes.

What I was after was to set the 3 fields

1) dob_day

2) dob_month

3) dob_year

then from those fields the DOB field is then set

Is this possible

the same as what you gave me at the start but reverse

from dob_import field

thanks

Craig

[Vaughan]

I was looking at the TextToDate() function a couple of days ago, comparing it to the Date() function. At first they looked very similar and I wondered why have them both, and began to conside whether TextToDate would be better then Date which I use almost exclusively.

As I read it, TextToDate requires the date be inserted in the same order as the localised date format. This means that a calc made for Australian format dd/mm/yy won't work correctly on a US system that uses mm/dd/yy. The Date() function has no such problem.

Correct me if I'm wrong...

[Lee Smith]

Hi Vaughan,

Not sure I'm understanding what you mean. If this is not desplaying as Craig wants, my first response would be to change the format at the layout level (i.e. day, month, year). However, if the calculation is not working are you saying that he then needs to use the TextToDate function? I would think that all he would need to do is change my formula of left and middle to produce it for him locally.

Lee

[Vaughan]

Sorry if I wasn't clear Lee. I was comparing the two functions that generate dates.

The FMP Help says this about the TextToDate() function:

Returns dates in text as data type Date, for use in formulas involving dates or date functions. The format of the date supplied must be the same as the date format on the system where the file was created.

It's th second line that's the killer. Here in Australia I'd have to code the function as "TextToDate(dayfield & "/" & monthfield & "/" & yearfield)" and it'd work sweet. Until somebody with US date format opened it and chose to set system formats to their own because it'd now be in the wrong order (month, day, year).

The Date() function offers no such challenge.

I guess the TextToDate function would be good when a person enters the whole date into a text field, separators and all, and it needs to be converted into date. In this case as long as the user typed the date in the appropriate format it'd work well.

[Ugo DI LUCA]

Yes. Very interresting. So text to date is "local" vs Date is "universal"

[Vaughan]

Yes, that's the way I read it.

I can see why both are useful functions. But TextToDate has a gottcha.