Jump to content

Year (TABLE::DATE) partially working


This topic is 2749 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by fm8443
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 )
)

 

  • Like 1
Link to comment
Share on other sites

This topic is 2749 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.