7 Replies Latest reply on Nov 8, 2011 9:18 AM by philmodjunk

    FMP 11 - Date calculation just not working

    niknek22

      Title

      FMP 11 - Date calculation just not working

      Post

      I'm a total 'newbie' trying to work with FMP 11, and my first stab a calculated field has failed and I can't figure out why.

      I simply want to calculate the elapsed days between two dates and according to 'The Missing Manual...' the calculated field should be formatted as follows...

      End Date - Start Date

      However, all I get in the calculated field is '?'.

      Can anybody point me in the right direction please?

      If I can't get a simple calc. like this to work, what chance have I got with something a bit more challenging!

        • 1. Re: FMP 11 - Date calculation just not working
          niknek22

           Further to my original post, it appears that my calculate field was too small. When I extended it I could see a numerical result, but it obviously isn't 'days'.

          It is telling me that the elapsed days between 20/10/2011 and 01/11/2011 is 21214022!!!

          Am I correct in thinking that this might be 'seconds'?

          • 2. Re: FMP 11 - Date calculation just not working
            niknek22

            After a bit more investigation, the result of 21214022 does not appear to be 'seconds' because that translates into something like 245 days, which is obviously incorrect.

            So where am I going wrong? 

            • 3. Re: FMP 11 - Date calculation just not working
              philmodjunk

              Check your field data types. Dates do record their data in terms of days elapsed since 12/31/0000. TimeStamp fields record data in similar fashion, but in seconds.

              If you want elasped time in days, make sure that both End Date and Start Date show "Date" as their field types in Manage | Database | Fields.

              If you need them to be time stamp fields, then you'll need to include a conversion factor that changes seconds into days.

              • 4. Re: FMP 11 - Date calculation just not working
                niknek22

                 Thanks Phil, that solved it.

                I had assumed that the Start Date and End Date fields would automatically be 'Date' fields simply because I had attached the 'drop-down calendar' to them, but no so. As you had suspected, they were still formatted as 'Text' fields.

                However I'm still having problems working with Current, or System, date. I can't seem to find out what to call in a simple calculation. The '//' symbol doesn't work, neither does CurrentDate, Current date or Today.

                Just what is the correct nomenclature for this?

                • 5. Re: FMP 11 - Date calculation just not working
                  philmodjunk

                  Look up the Get function in Filemaker help. This is a "swiss army knife" type of function that can "get" all kinds of different values so it's very much worth spending some time to get to know what it can do.

                  In this case, Get ( CurrentDate ) will return the date of your computer's system clock. Be careful, however, when you use this in the expression for a calcualtion field. If you use it in such a calculation, you must also click the storage options button and specify that the field be unstored. If you do not, the value returned by your calculation field will not update as time passes. For the same reasons, you cannot use this function in an auto-enter calculation if you need it to update with each new day.

                  • 6. Re: FMP 11 - Date calculation just not working
                    niknek22

                    Thanks Phil,

                    That's rather worrying.

                    You seem to be telling me that, in FMP 11, it isn't possible construct a database which will automatically (day by day), calculate the number of elapsed days from a given (static) Start date.

                    I find that very hard to believe, and if it is the case, it highlights a rather basic deficiency within FMP 11.

                    • 7. Re: FMP 11 - Date calculation just not working
                      philmodjunk

                      On the contrary, it can do exactly that, but you must specify the correct field options in order for this to happen.