Is this a one-time thing, or do you need to do this regularly?
It's just a one-time thing. I could probably convert the dates in the spreadsheet before I import, but I wondered whether I could just set Filemaker to use US dates, import the data as is, and then set filemaker to Australian dates and have it do all the work for me? Is there an easy way to switch within Filemaker itself?
It's sort of possible, but I have found out that sometimes there are issues with the original format "sticking" to the imported data. The best way IMHO is to make a clean conversion either in an external application or by importing to a temp table/file.
If you import the date into a text field, you can convert it by using a calculation field (result is Date) =
Let ( [
m = Left ( textdate ; 2 ) ;
d = Middle ( textdate ; 4 ; 2 ) ;
y = "20" & Right ( textdate ; 2 )
Date ( m ; d ; y )
Note that this assumes that the original format is mm/dd/yy (your example doesn't make it clear), and that all dates are in the 21st century.
Unfortunately this database has many dates from last century. I just changed my computer system to US dates, opened filemaker, imported the data, closed filemaker, changed back to Australian dates and reopened filemaker. Seems to have worked.
Yes, it works - but what happens when you export your data, for example?
This a more general comment from a new user, also down under.
I to have wrestled with the date formatting to get a DDMMYYYY,
as we use in NZ. Have gone to the Format Date menu item
and set it up. If I use the built in calender to enter things like
date of birth, it works properly. However if I try to to enter a
date from the keyboard (as I have been doing for 30 years),
Filemaker puts it back into the month first system, even though
I have instructed the program to retain date as entered.
A Filemaker bug I think.
What you do in Layout mode affects only how the date is DISPLAYED. The method of entry depends on the formats that the file is using at the moment. A file created on a computer set to use MM/DD/YYYY will expect dates to be entered in this format - unless you change this in File > File Options…
I have worked out a reliable way to change the MM/DD/YY dates to DD/MM/YYYY while importing from Dataease. My export format is a simple tab delimited table, so I open it in Excel and tell it to change the dates to DD/MM/YYYYduring import. Then I import from Excel into filemaker.
I would think this is dicey in that what if some are 1 digit numbers?
I would probably do:
(If date = 22/10/2009)
values = Substitute(date; "/"; "¶") //paragraph symbol ;
GetAsDate(GetValue(values ; 2) & "/" & GetValue(values; 1) & "/" & GetValue(values; 3));
I'd probably first import them into textfields in one table and then use a script to import them into the new table with the above calculation.
Actually, Dataease 4.5 is a very old DOS application with totally specified formats for every field, so there are no single digit numbers in the dates. It is so old that 12/31/99 is later than 01/01/00!!!!!!! But it is also VERY efficient and fast if you understand the architecture. We are only changing because our IT people can't network it. (Yes, I know you can run it with a Novell network but they don't even want to think about it)