Date Conversion

[nightdesigns]

I'm pulling dates out of the headers of e-mail's and unfortunately they're not all formatted the same. I have two fields, date received and time received and I need to parse each of these dates and times into those fields in a format that FM likes. Any ideas on the best way to do this. As you can see, they're all somewhat consistent, except for that second one. Also, i need all the times set to Pacific time (-0700)

Sat, 04 Sep 2004 01:49:38 -0400

4 Sep 2004 05:48:39 -0000

Sat, 04 Sep 2004 01:38:42 -0400

Fri, 03 Sep 2004 10:15:17 -0400

Mon, 30 Aug 2004 03:05:56 -0400

Thu, 04 Mar 2004 02:08:52 -0500

Thanks

[Lee Smith]

What platform, operating system and version of FileMaker are you using.

You need to update your profile, there is a link under your name in you post "profile".

Lee

[nightdesigns]

I selected it in the little drop down menu above the post box, but i guess it doesn't post it.

Anyway, FM5 on Win2k

[Lee Smith]

Use the Link below you Name and Loc Orange County, CA,

I.e.:

update your [color:"blue"]profile

[RalphL]

I put together a sample file which I think does what you want but it is in FM7. So here is what I did. I started with a text field "String" I made a global field g_Months = "JanFebMarAprMayJunJulAugSepOctNovDec"

The calculations for the following fields are:

Day = Case ( WordCount ( String ) = 5; LeftWords ( String ; 1 ); MiddleWords ( String ; 2 ; 1 )) [number]

MonthName = Case ( WordCount ( String ) = 5; MiddleWords ( String ; 2 ; 1 ); MiddleWords ( String ; 3 ; 1 )) [text]

Month = Case ( not IsEmpty ( MonthName ) ; (Position ( g_Months ; MonthName ; 1 ; 1 ) + 2) / 3 ; "") [number]

Year = Case ( WordCount ( String ) = 5; MiddleWords ( String ; 3 ; 1 ); MiddleWords ( String ; 4 ; 1 )) [number]

Date = Date ( Month ; Day ; Year ) [date]

Time = TextToTime ( Case ( WordCount ( String ) = 5; MiddleWords ( String ; 4 ; 1 ); MiddleWords ( String ; 5 ; 1 ))) [time]

Zone = TextToNumber (Right (Trim (String ); 5 )) / 100

I used a new type "Timestamp" for the rest of the calculations. I haven't tested this but it should work.

TimeStamp = (Date * 24 * 3600) + Time [number in seconds]

To correct to your time zone

TimeStamp_6 = TimeStamp + ((- 6 - Zone) * 3600) [number in seconds]

The date = Int(TimeStamp_6 / (24 * 3600))

The time = Mod(TimeStamp_6, (24 * 3600))