Use real date fields, not text wherever possible. Date data in fields of type date are stored as an integer that counts the number of days from 12 / /31 / 000 to the date stored. The format displayed in a report then becomes a data format setting you can specify for that field.
Enter layout mode and select the date field.
Use the data formatting section of the inspector's data tab to specify the desired format.
If you can't import the data into a date field, such as your 12May1957 example, you'll need to use a calculation field to convert it into a date:
Let ( [ D = Left ( DateText ; 2 ) ;
Y = ( Right ( DateText ; 4 ) ;
Mname = Middle ( dateText ; 3 ; Length ( dateText ) - 6 ) ;
M = Ceiling ( Position ( "janFebMarAprMayJunJulAugSepOctNovDec" ; Left ( Mname ; 3 ) ; 1 ; 1 ) / 3 ) ] ;
Date ( M ; D ; Y )
Choose Date as the result type for this calculation and you can format the displayed date as described at the start of this post.
Note: this calculation assume two digit numbers for all day values so leading zeroes are needed for days 1 - 9.
Thanks for your instructions.
I do have the Date of Service data entered as 'Date' type data under Field Type. (it is in the dd/mm/yyyy rather than the Excel style dd-mm-yyyy if that matters?) I looked again though & realized that HanDBase allows me to enter the Date data in the proper ISO format of yyyy-mm-dd but that this gets converted by the Desktop version of the program to the dd-mm-yyyy format & then to CSV etc.
I have tried your suggestion before. When I go to Layout Mode & change the Date Format from 'As entered' to the '2003-12-25' option, the dates in the field are replaced by a '?' character. Changing the leading zero option has no effect on this. Any suggestions as to why this is not working?
When I try to convert the Text Field dates under the 'DOB' field (Formatted as Text as ddMMMyyyy) I see the following:
I go to Browse mode & click on the DOB field > Field Type > Calculation and enter your formula specifiying result as 'Date'. It gives me an error message: 'The specified field cannot be found.' while highlighting the first 'DateText' occurrence in the formula. I tried changing this to 'DOB' in case this was the problem but no success.
I may be dim but I'm not seeing the solution readily.
Thanks for your help.
Any suggestions as to why this is not working?
Resize the field to make it wide enough to correctly display the date. The format change is producing data that needs more space to correctly display.
I tried changing this to 'DOB' in case this was the problem but no success.
Each instance of "DateText" needs to be replaced with the name of your text field. There are 4 such references in the calculation.
Thank you Phil. I really appreciate your help.
One issue solved, one to go...
I got the Text to Numerate date conversion to work (I just had to remove an extra Bracket on the second line of the formula where it read:
Y = ( Right ( DateText ; 4 ) ; Thank you very much for that.
As for the first date issue though; Giving it a very large column width made no difference. I am still seeing a lonely question mark instead of a ISO compliant date. Any other suggestions?
Is it possible that the FM Pro has a bug in the date format options?
I have the dates listed as dates & they are being sorted appropriately acording to their dd/mm/yyyy formatting. I have tried reimporting these with dd-mm-yyyy separators but still get the '?' response.
I just tried exporting the dates from HanDBase in mm-dd-yyyy format & tried to convert them to ISO format in FM Pro -> same result: a '?' for each entry in that field.
Is there an equivalent calculation field that could 'Manually' switch the order of the digits to change the field entries from dd-mm-yyyy to yyyy-mm-dd?
I don't mean to be so lazy asking for the code but if i could find an easy reference to the programming syntax I'd be wiling to write this calculation field code myself.
It sounds like this data is not being recognized by FileMaker as an actual date and is instead treating it as text.
If you open manage | database | fields and find this field's field definition, can you confirm that this field is really of type date and not of type text?
Does mm/dd/yyyy match your current system date settings for your computer?
If you type a date into this field in the mm/dd/yyyy format do you get an error when you exit the field? (Examine it with and without the data formatting enabled.)
My Computer system settings (Mac running OsX 10.7) have dates in the yyyy-mm-dd format.
I confirm that the imported Date of Service field ('Date_Pr') has field property set as date.
When the field format is set to: 'As entered' and when I try entering a new record in the 'Date_Pr' field it will reject anything but the system settings' preference of yyyy-mm-dd. All the imported dates on the fields above the new record are displayed in their imported format of dd-mm-yyyy.
When I switch to field format specified as 2003-12-25 I am obliged to enter new dates in the yyyy-mm-dd format and the old imported dates are displayed as '?'.
It looks to me that I will need to write some sort of calculation field to convert the imported data to the yyyy-mm-dd format & to conform with the system and FM Pro settings...?
The system settings are affecting the import. This part of FileMaker is a bit buggy and I've seen other reports of issues encountered that aren't totally dissimilar from this.
I think you may have to import your date data into a text field and then use a calculation to convert it into a date:
Let ( dte = YourDatefield ; date ( left ( dte ; 2 ) ; Middle ( dte ; 4 ; 2 ) ; Right ( dte ; 4 ) ) )
Note, there's a trick I learned from LaRetta that can do the conversion during the import process you can try if you are interested:
Define two global text fields for each of your two date fields. On the actual date fields, define the calculations we have produced here as auto-enter calculations that take the text in the global date field and compute the needed date. Then, during import, map the imported dates to import into these global text fields and enable the auto-enter option in the last dialog to appear before the import starts up.
This leaves your date data in date fields where you can specify the desired display format in a format that permits editing the dates in these same fields.
This will work just fine. I created a calculated field column called 'DOS correct'; it imports the previous fields to the same old field but also gives me a calculated field with the new correctly formatted dates...but, the formula you have offered is converting the import data is if the import data was in the mm/dd/yyyy format giving me dates in the future (2013 etc.) when it picks up a date like 21/10/2011.
Could you suggest an amended formula with this corrected? I think this will have solved the problem for me.
Let ( dte = YourDatefield ; date ( Middle ( dte ; 4 ; 2 ) ; left ( dte ; 2 ) ; Right ( dte ; 4 ) ) )
The parameters for the date function, no matter what your system settings, must be in month ; day ; year order.
Thank you very much Phil.
This totally solves my problem. (I should probably learn the syntax for these calculations but I really am only using Filemaker as a portal to outputting a custom form and probably will have little need to learn this...(Until next time maybe)).
Thanks again. Your help has saved me days of research & floundering in frustration. I really appreciate it.