Changing the date format

[prissypan]

Hi guys,

Any ideas how i can convert this date format "2 Feb 2004" to the format of the Filemaker (ie MM/DD/YYYY).

I have been pondering all afternoon about how should i go about solving this problem. What i already did was to separate the date, and made it into this format "Feb/2/2004". Please suggest some methods which i can use to change the alphabetical "Feb" to a numerical value "02", and do so for all other months in the year as well?

-Pris-

[murray]

If the date is in a field and the field is defined as a date field, you can change the displayed date by using the Format->Date settings.

If it is a text field, perhaps you can try using the Substitute function combined with Case function in a calculation that will substitute "01" for Jan, "02" for Feb etc. This will work if the value is always "Feb" but if the Month varies in it's value (Feb, February, Feb'y etc), you may need to do a PatternCount on "Feb" to set the substitution, accounting for different variations which may occur which will get messy. You may better to use the '/' as a position to strip the value to the left and substitute that.

Hope this is a start - I suspect someone will come up with a foolproof method.

Good luck.

Murray

[prissypan]

Hi murray,

thanks for your fast response,

I managed to change the "Feb" into a "2". The value of month will always be a 3 character.

Right now i have another problem which is, how do i make the change for all the months into a number?

Do i do it by calculation? Or script? I'm currently tryin to make it work using script but i have no idea how to get a script to work, or if my script is workin alright.

Is it possible for the case function you mention to do the changing for all 12 months? I would very much prefer to use calculation to do it as i still can't understand the scriptin language of Filemaker.

Below is some codes i did using calculation and script, please advise.

Calculation Method(change):

If( Middle( regDateMask, 1, 3) = "Feb",Substitute( regDateMask, "Feb", "2"), "Hi")

Script Method(failed to change):

If( Middle( regDateMask, 1, 3) = "Jan")

Replace Contents(Substitute( regDateMask, "Feb", "1")

else

If( Middle( regDateMask, 1, 3) = "Feb")

Replace Contents(Substitute( regDateMask, "Feb", "2")

End if

End if

--------------------------------------------------------------

Is there anything wrong with my script?

Btw how do you write scripts in Filemaker? By using the Scriptmaker?

i tried copying my codes but didn't manage to find that function.

How do you guys copy the scripts?

-Pris-

[murray]

Try this - it's not very elegant but it works. I'm positive there is a neater more compact solution.

The "Proper" function just accounts for irregularities in the case of the entered data.

As for scripting, look for all the unlocked examples that you can find on these forums and look at their scripts. I'm very much a learner myself and it is often possible to achieve a result with a calc rather than a script.

Good luck,

Murray

date.zip

[BobWeaver]

If the date text is originally in the format "2 Feb 2004" you can get the month number with this formula:

Position("xxjanfebmaraprmayjunjulaugsepoctnovdec", MiddleWords(date, 2, 1), 1, 1)/3

[Lee Smith]

Hi Bob,

I had this calculation, but could *not* get the start number right. So to convert the whole date, this calculation seems to work great.

Date(

Position(" JanFebMarAprMayJunJulAugSepOctNovDec", MiddleWords(DateText, 2, 1),1,1)/3,

Left(DateText, 2),

Right(DateText, 4))

The "Date Text" would be the current field where the date is 2 Feb 2004.

HTH

Lee

[prissypan]

Hi guys thank you so much for the help.

But somehow it doesn't work, what should my calculation result be?

What i would like is for the calculation to automatically change the text value of all months to number. Eg Jan = 1, Feb = 2, Mar = 3 etc.

Can i also know what is the "/3" for?

Looking forward to your help.Thank you!

[prissypan]

Thanks Lee, Bob and Murray.

Manage to get the conversion working.

Thank you so much!

Cheers,

Priscilla