Year (TABLE::DATE) partially working

[fm8443]

I have a TABLE::DATE field that has content with varying date types:  e.g. 1) 12/12/2015 and 2) Dec 12, 2015.  The field is type "Date".

I have another field TABLE::c_YEAR that is of type "calculation" returning a "Number".  
The calculation is Year (TABLE::DATE). This works for dates as in #1 (e.g. 12/12/2015) but fails for dates such as #2 (e.g. Dec 12, 2015).

Any idea why and how to fix (within the TABLE::DATE field)?

(I am importing the problem rows from an Excel XLSX file (where the dates are in Oct 12, 2016 format) in an FM Script with Auto-validation turned off on import.  Not sure if this is an issue)

Edited October 14, 2016 by fm8443

[Lee Smith]

This is a date in the US 12/12/2015

This is NOT a date in the US Dec 12, 2015

 You will need to convert the Dec 12, 2015 to 12/12/2015.

I see you have FileMaker Pro Advance, you should be able to find a Custom Function to convert these.

Look at the Date Function in the Functions for date here http://www.filemaker.com/help/15/fmp/en/#page/FMP_Help%2Fdate.html%23wwconnect_header

Lee

[comment]

3 hours ago, fm8443 said:

I am importing the problem rows from an Excel XLSX file (where the dates are in Oct 12, 2016 format)

Are you sure about that? I am not able to test this at the moment, but I believe that if the Excel cells contain dates formatted as MMM D, YYYY, they should import as valid dates into a Filemaker date field. If they import as "Dec 12, 2015" (i.e. as strings, not as dates), they probably are not dates in the source Excel file either.

In any case, if you want to fix the imported "dates", you must find them (using the question mark as the criteria) and convert them to the date format used by your file. And if you plan on doing this import repeatedly, you need to script the process (for example, import the "date" into a text field and populate the real date field by calculated value).

 

Edited October 14, 2016 by comment

[fm8443]

Comment -- I got it wrong. The dates in my excel file are in general format.  So they are coming in as text.  I want to fix this in fm in either calculations scripting or a function.  Not practical to edit the source or really the rows in fm.  Really looking to populate my year month and day fields properly. May use the left right middle functions if there's not a better way?

[comment]

26 minutes ago, fm8443 said:

May use the left right middle functions if there's not a better way?

You can use =

Let ( [
mmm = LeftWords ( datestring ; 1 ) ;
m = Ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; mmm ; 1 ; 1 ) / 3 ) ;
d = MiddleWords ( datestring ; 2 ; 1 ) ;
y = RightWords ( datestring ; 1 )
] ;
Date ( m ; d ; y )
)

 

[fm8443]

Works perfectly.  Thanks.