1 2 Previous Next 16 Replies Latest reply on Oct 21, 2015 9:34 AM by beverly

    Date format question for those outside the U.S.

    AllegroDataSolutions

      I know that some countries reverse the order of the Month and Day from the conventions used here in the U.S. I am wondering what format is used if the day of the week is included, and where commas (if any) are needed.

       

      For example, when the following date is referenced in the U.S. ...

       

           Monday, August 31, 2015

       

      ... would the international equivalent be

       

           Monday, 31 August, 2015

      or

           Monday 31 August 2015?

       

      Thanks.

        • 1. Re: Date format question for those outside the U.S.
          erolst

          There is not really an “international” format.

           

          Here's the format commonly used in Germany

           

          Montag, 31. August 2015

           

          Note that a comma is used to offset the day name, but not the year, and that the period for the day denotes that this is an ordinal number.

          • 2. Re: Date format question for those outside the U.S.
            Menno

            in the Netherlands it looks like:

            maandag 31 augustus 2015

            so no capitals.

             

            this doesn't really clarify it I suppose, sorry. You'll need to look it up for each country. As a general rule a date is always constructed as day, month, year in most central european countries.

            • 3. Re: Date format question for those outside the U.S.
              AllegroDataSolutions

              Actually, your answers do help. I am working on a new release of Allegro Mini-Calendar, which is a customizable calendar source code Made-for-FileMaker solution. It's written in English, but has a large body of international users, who modify the text for the language needed. I just wanted one example of a different date format, to demonstrate how a drop down list and formula could be expanded to fit any formats required. So, for example, a developer with a client doing business in the U.S. and one or more other countries could include an option for each to show the days of the week in the correct format for each location.

               

              Thanks. (Both your answers should be marked as correct, but I was unable to mark more than one.)

              • 4. Re: Date format question for those outside the U.S.
                erolst

                allegro wrote:

                Both your answers should be marked as correct, but I was unable to mark more than one.

                 

                Well, you could “Like” mine …

                 

                btw, the System Preferences (OS X) / Settings (Windows) may be a nice playground to check out the default settings for a wide range of countries.

                • 5. Re: Date format question for those outside the U.S.
                  CarstenLevin

                  In Europe and many other countries around the world we are using two formats:

                  For today the 30th of August 2015

                  • 15/08/30 which sort really well or 2015/08/30
                  • 30/08/15 or 2015

                  But never the american way, putting the day/date in the middle. This method does not really build up logically and is different from all the other English and non English speaking country.

                  But it is not a problem for FileMaker. FileMaker is not storing the date in the date format but as days since your 0.

                  According to articles on the problem more and more are using the dd/mm/yy or eye format instead of the mm/dd/yy or yyyy.

                  The US authorihies are increasingly asking you to fill in forms with dd/mm/yy instead of the mm/dd/yy format. Maybe this strange anomality is on it's way out?

                  • 6. Re: Date format question for those outside the U.S.
                    beverly

                    I prefer YYYYMMDD as it SORT alphanumerically correctly (or YYYY-MM-DD) regardless of international standards.

                     

                    but to add to the discussion:

                     

                    https://en.wikipedia.org/wiki/Date_format_by_country

                    http://calendars.wikia.com/wiki/Date_format_by_country

                     

                     

                    On Aug 30, 2015, at 5:26 PM, Carsten Levin

                     

                     

                    Date format question for those outside the U.S.

                    reply from Carsten Levin in Discussions - View the full discussion

                    In Europe and many other countries around the world we are using two formats:

                    For today the 30th of August 2015

                    15/08/30 which sort really well or 2015/08/30

                    30/08/15 or 2015

                    But never

                    the american way putting the day/date in the middle. This method does not really build up logically and is different from all the other English and non English speaking country.

                    But it is not a problem for FileMaker. FileMaker is not storing the date in the date format but as days since your 0.

                    According to articles on the problem more and more are using the dd/mm/yy or eye format instead of the mm/dd/yy or yyyy.

                    The US authorihies are increasingly asking you to fill in forms with dd/mm/yy instead of the mm/dd/yy format. Maybe this strange anomality is on it's way out?

                     

                    • 7. Re: Date format question for those outside the U.S.
                      AllegroDataSolutions

                      Remember this solution is source code for integrating a calendar into FileMaker solutions. The intent is to make it look like printed calendars that are used for the region. The point is not to pick the "best" or most logical method of displaying the date, but to give the user the option to make choices that are appropriate for the region, which can be different from the format used by the OS.

                      • 8. Re: Date format question for those outside the U.S.
                        AjEGfmTech

                        I prefer that date format as well Beverly. Do you know of a simple/straightforward way to take an FM date (which is presumably stored as MM/DD/YYY) and modify it to be 'YYYYMMDD'? I presume that some method of creating a calc field that would use GetAsText (Date) and some Replace commands? I have used the one below to convert MM/DD/YYYY into MMDDYYYY, but am not sure how to flip the order?

                         

                        Replace (Replace ( (GetAsText ( Date )) ; 3 ; 1 ; "" ) ; 5 ; 1 ; "" )

                        • 9. Re: Date format question for those outside the U.S.
                          beverly

                          sure! use the date functions:

                           

                          Year(myDate) & "-" & Month(myDate) & "-" & Day(myDate)

                           

                          It will take any international format and give you the correct values.

                           

                          To "pad" and make consistently spaced for best sorting, I use this:

                           

                          Year(myDate) & "-" & Right("00" & Month(myDate); 2) & Right("00" & Day(myDate); 2)

                           

                          Use or don't use the "-", your choice. Remember to place this result into a TEXT field.

                           

                          beverly

                          • 10. Re: Date format question for those outside the U.S.
                            AjEGfmTech

                            Thanks Beverly! So, do I understand correctly that your calc would give the following result?

                            myDate = 10/20/2015

                            YourCalc = 2015-0010-0020

                             

                            Is that right? I've noticed an issue where users do not enter a two-digit month or day or a four-digit year. Is there a way to make sure that their entry is MM/DD/YYYY ? I was under the impression that internally FM stored it that way, but maybe that's not the case?

                            • 11. Re: Date format question for those outside the U.S.
                              beverly

                              no, you are prepending the "00" to the month or day, and then taking the RIGHTMOST 2 characters. That way your day or month will always have a leading 0 is if normally would have one digit (1-9).

                               

                              the calc will result in 2015-10-20

                               

                              Beverly

                              • 12. Re: Date format question for those outside the U.S.
                                user19752

                                When you want to 'show' a date as 'YYYYMMDD', you can use date formatting in layout mode.

                                 

                                Another simple calculation is ,

                                Year ( aDate ) * 10000 + Month ( aDate ) * 100 + Day ( aDate )

                                • 13. Re: Date format question for those outside the U.S.
                                  beverly

                                  This makes a number, correct, user19752? While there are unlikely many records with "year" less that 1000, anyone doing geneology knows that there are dates before then.

                                   

                                  So I use the "Text calculation" method to created the 'date' which becomes 'numerically/alphabetically' sortable. And yes there may be days or months with "00" value in genealogy (if a full date is not known). These are still sortable as text.

                                   

                                  Beverly

                                  • 14. Re: Date format question for those outside the U.S.
                                    user19752

                                    Yes it is number, but if used in field calculation, the result can be text.

                                     

                                    If there are year less than 1000, number can be sorted as is, but the text need 0 prefix on year, same as month and day you did.

                                     

                                    In other db systems, 8 digit number use half or less storage than 8 characters text, but this may not applied to FM...

                                    1 2 Previous Next