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'?
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?
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.
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?
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.
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.
On the contrary, it can do exactly that, but you must specify the correct field options in order for this to happen.