10 Replies Latest reply on Mar 3, 2012 2:24 PM by comment

    Newbie question on date field

      I've imported my records from Excel, and my layout includes a date field, which I thought was formatted as dd/mm/yyyy. I entered a new record with a date in this format, but noticed that the record did not sort into its correct place when sorted by date. I then noticed that clicking into the date field of any of the imported records displayed the date as the following example: "02/03/2012 12:00". How has the time element got into this field, and how can I get rid of it from all records? There are too many to retype individually).

      Appreciate any advice.

       

      Steve

        • 1. Re: Newbie question on date field
          comment

          When you import, all kinds of junk can get in - Filemaker is quite tolerant in this aspect. The real question is whether the imported data is correctly interpreted as dates. You can check this by searching the field for ? (question mark). Any records found have invalid dates.

           

          The other thing you want to pay attention to is the date format: ""02/03/2012" is valid both as d/m/y and as m/d/y. The way the field is formatted to display on the layout is irrelevant here. What matters is the date format being used by the file.

           

           

          If all the imported dates are valid and correct, you can replace the field contents with calculated value =

           

          GetAsDate ( YourDatefield )

          • 2. Re: Newbie question on date field
            Stephen Huston

            I agree with Michael on how to clean up your data once in FileMaker.

             

            But also check your source data. It sounds like you are importing a Dat-and-TimeStamp value rather than just a date. If you can clean up the source data, you won't have to do this after each import.

             

            If you can't control the source data, you might look into applying an auto-enter calc on the date field using the formula Michael provided so that the fields will self-clean themselves as the data is entered.

            • 3. Re: Newbie question on date field
              psijmons

              Then there is the little quirk from Excel that (irrespective of the date format defined in the excel cells) in the background this date is calculated as the number of days from 01-01-1900 while the standard is 01-01-0001.

              This took my by surprise the first time I stumbled on this, so beware when you do an auto-enter calc on the field that you take this into account.

              • 4. Re: Newbie question on date field
                comment

                psijmons wrote:

                 

                Then there is the little quirk from Excel that (irrespective of the date format defined in the excel cells) in the background this date is calculated as the number of days from 01-01-1900 while the standard is 01-01-0001.

                 

                I am not sure that's an entirely accurate description. AFAIK, Excel calculates dates as the number of days elapsed since either 1/1/1900 or 1/1/1904, depending on the platform (and optionally overriding setting). I am not aware of any "standard" in this regard; Unix, for example, considers 1/1/1970 as the beginning of all time.

                 

                If there is one thing that can be learned from this, it's that Excel is a very poor choice for an intermediate format.

                • 5. Re: Newbie question on date field

                  Thanks to all who replied on this.  I won't be importing any more records, so now I just need to know - how does one use the GetAsDate function Michael mentioned?  I'm still trying to wean myself off MS Access, where I would have used an update query.  What is the equivalent procedure in FM?

                  Thanks again.

                   

                  Steve

                  • 6. Re: Newbie question on date field
                    comment

                    eaststander wrote:

                     

                    how does one use the GetAsDate function Michael mentioned?

                     

                    Show all records, click into the date field and select Records > Replace Field Contents… > Replace with calculated result =

                     

                    GetAsDate ( YourDatefield )

                     

                     

                    Having a backup is highly recommended, as this cannot be undone.

                    1 of 1 people found this helpful
                    • 7. Re: Newbie question on date field
                      skywillmott

                      Hi,

                       

                      Are you using British date format in your file (dd/mm/yyyy) or US style (mm/dd/yyyy)? No idea why the US format is like that... Seems very illogical.... Anyway I am uk, and perhaps the way I would do this is temporarily create a 'Date' type field, named 'new date ', then use Replace with Calculated Result on that field , the calculation being LeftWords (imported_date_field; 1)

                       

                      Then, make sure that the 'imported_date_field' is actually defined as a 'Date' type field, rather than 'Timestamp' and do another Replace with Calculated Result on that field, replacing its contents with the temporary date field.... You can then delete the temporary date field from your table..... You might not need this step, but if any scripts, sort orders etc rely on the original imported date field, then probably best to do this....

                       

                      Hope this helps,

                       

                      Sky

                      1 of 1 people found this helpful
                      • 8. Re: Newbie question on date field

                        Problem now fixed - thanks for all the help, suggestions.

                         

                        Steve

                        • 9. Re: Newbie question on date field
                          psijmons

                          Ah, Michael, it is even worse then, the start date is system dependent?

                          I never realized that, checked in wikipedia

                          http://en.wikipedia.org/wiki/System_time

                           

                          so much for standards, eh?

                          • 10. Re: Newbie question on date field
                            comment

                            Oh no, it's  worse than that: it's the start date of Excel that's system dependent:

                            http://support.microsoft.com/kb/180162

                             

                            And if you think that's bad, see:

                            http://support.microsoft.com/kb/214326