Importing date fields

[Tmonk]

I'm trying to create a program that will take the data from our custumer management app and allow us to run a monthly tax due report. However, when the "payment date" data is exported from the custumer management app(not a fm app) it is formatted in one of two ways in the same field. Ex. 12/1/2006, which imports into my fm app fine, but the other way it is formatted,ex. 2007 112, does not. The program dumps the data in dbf format. Is there a way that during the import data step, I can change the date to import properly. Thanks

[comment]

That's not very clear. If the other program can export dates in a way that Filemaker can read, then why not make it do so consistently?

If not, you will need to import the 'date' into a text field, and auto-enter a calculated date into the real date field. But more details about the format are required. For example, assuming "2007 112" means December 1, 2007, what does February 11 look like?

[Tmonk]

I have no control over how the customer program formats its data when exporting. I am trying to take that data and import it into a fm app. However, what is happening on export is that all the date info is formatting two ways at the same time . Some records show up as "mm/dd/yyyy" (12/21/2006) and others "yyyy mmdd" (2006 111). I don't know why this is and I have no control over it. When I try to import this field into a fm app all the "yyyy mmdd" dates are left blank for that record. Since the date is formatted in two different ways across all the records I'm looking for a way to correct that on import.

[comment]

If you don't know in advance how the dates are going to be formatted, you will need to construct a calculation to tell them apart, for example by detecting the space in one of the formats. Something like:

Case (

PatternCount ( ImportedDate ; " " ) ;

;

GetAsDate ( ImportedDate )

)

The reason for the questions marks is that "2006 111" is NOT "yyyy mmdd". If it were, there would be two digits for both the month and the day. As it is, it is ambiguous: it could be January 1 or November 1. I cannot suggest a method to extract the correct date without knowing the exact format.

[Raybaudi]

Hi

what about a calc field ( result date ):

Case (

PatternCount ( ImportedDate ; "/" ) ; ImportedDate ;

Date ( Middle ( ImportedDate ; 5 ; 2 ) ; Right ( ImportedDate ; 2 ) ; Left ( ImportedDate ; 4 ) )

)

This is valid if the "space" is present only for months below october, in other words if the length of the ImportedDate ( w/o "/" ) is always 8

In that case, even this may work:

Case (

Length ( ImportedDate ) = 10 ; ImportedDate ;

Date ( Middle ( ImportedDate ; 5 ; 2 ) ; Right ( ImportedDate ; 2 ) ; Left ( ImportedDate ; 4 ) )

)

BTW: [color:red]Tmonk didn't replay to your question:

For example, assuming "2007 112" means December 1, 2007, what does February 11 look like?

[Tmonk]

Sorry about not answering the feb 11 question. Looking through the date field in my exported file it appears as "2006 211".

I am going to try your suggestions. Thanks!