4 Replies Latest reply on Oct 19, 2014 7:03 AM by philmodjunk

    Canadian vs US Dates

    RBCohen

      Title

      Canadian vs US Dates

      Post

      I am importing data from OpenOffice Calc ("Excel"). The dates are set as Canadian DD/MM/YYYY 06/01/2014 = Jan 06 2014. I set FileMaker to DD/MM/YYYY but it's reading as US Date  = June 01 2014 while showing 06/01/2014 (when I click on the field and the Calendar pops up)

       

      Using FileMaker Pro Advanced 11.0v4

        • 1. Re: Canadian vs US Dates
          philmodjunk

          Sounds like the dates are importing as text rather than date type data. Please note that changing the display settings for dates in the Inspector's data tab only changes how the values are displayed, this does not affect how the data is stored.

          If you can't get the data to import as actual dates (in which case the format would no longer matter), you'll need to import the data as text and use a calculation to fix the dates and put actual date values into your date field.

          • 2. Re: Canadian vs US Dates
            RBCohen

            The OpenOffice Calc (Excel) dates are formatted as Date but perhaps it's because it's not Microsoft Excel original. Would you please explain the calculation steps I need to do to fix the dates?

            • 3. Re: Canadian vs US Dates
              rbc

              I did tons of experiments with the original data. It reads okay now in OpenOffice but not in FileMaker, I cannot get 08/02/2012 to read as "Canadian style" February 8, 2012. When I copy in the date (from source), formatted to DDMMMYYYY, it reads "US style" August 2, 2012. If I read the Help correctly, it says I have to set my OS to read that. I _cannot_ change my OS. It must remain as US for all my other applications.

               

              Using FileMaker Pro Advanced 11.0v4

              • 4. Re: Canadian vs US Dates
                philmodjunk

                You may need to use an auto-enter calculation to convert the dates during import. I'm surprised that this is necessary, however:

                Set up a second date field, DateField2, with this auto-enter calculation:

                Date ( Day ( DateField1 ) ; Month ( DateField1 ) ; year ( DateField1) )

                This calculation swaps out the day and month values for each other.

                You then need to select the "Enable auto-enter options" check box during import.