8 Replies Latest reply on Dec 7, 2011 4:03 PM by MicheleOlson

    Interval calculating from approximate dates


      I am looking for ideas on how to provide for calculating time intervals from approximate dates. For instance the user knows the exact date some occurance ends but only knows the year it began (or month and year, week month and year, decade, century, quarter and year, etc.). I wanst to return to the user the time interval over which that occurance happened. Obviously, I cannot give the user date fields in which to enter dates, since date fields require complete exact dates. I would have to give the user text fields to accept the dates, then calculate conversions from the text dates to data dates before calculating the interval, which would have to be expressed as an approximate interval unless both dates actually entered were exact. Suggestions welcome.

        • 1. Re: Interval calculating from approximate dates

          How about giving the user three number fields to enter into: Month, Day, and Year.


          They can fill in whatever data they do know, and you can use a calculation field to create the "real" date you want.






          Paul Spafford

          FileMaker Database Superhero,

          Spafford Data Solutions


          Tel: 613 838 9956






          FileMaker User Group in Ottawa:


          1 of 1 people found this helpful
          • 2. Re: Interval calculating from approximate dates

            Sounds workable Paul; thank you.  It could get a bit messy if I try to accommodate week precision, quarter precision, decade precision, century precision, etc.

            • 3. Re: Interval calculating from approximate dates

              Further to this, what would be standard practice: if a date is known only to month precision (week precision, year precision, etc.), should an interval calculated from that date be calculated from the beginning of that month, the middle of that month, or the end of that month?

              • 4. Re: Interval calculating from approximate dates

                Hi hopkins,


                I don't believe there is a universal standard formula for date estimation - the 'rules' vary according to context, however in a number of cases I've been involved with, the middle value (15th of the month, Wednesday of the week) has been used if nothing more specific has been provided.


                The exceptions to this have mainly been in cases where there is a presumption that whole periods (weeks, months etc) will be the most common format - such as where there's a convention that new staff always commence on a Monday, so if the start week is stated the presumption that the Monday was the start date would follow.


                On balance, unless there's a rationale for doing otherwise (or a client specification to the contrary), I'd suggest you use the middle date within a period whenever more specific data is unavailable.





                R J Cologon, Ph.D.

                FileMaker Certified Developer

                Author, FileMaker Pro 10 Bible

                NightWing Enterprises, Melbourne, Australia



                1 of 1 people found this helpful
                • 5. Re: Interval calculating from approximate dates

                  Thank you Ray,


                  Middle sounds most reasonable.

                  • 6. Re: Interval calculating from approximate dates

                    I needed to address a similar situation in which the date was unknown or approximate.  What I did was provide a text field for the user to input what they did know and with a custom function convert the partial date into a valid date.


                    Here is the Custom Function:




                    03/21/2007  1.0  KLN  Original Version

                    10/05/2007  1.1  KLN  Updated Year Calculation On Month/Year Entry

                    10/08/2007  1.2  KLN  Corrected Month/Year Calculation


                    PURPOSE:  To convert a "partial date" to a valid date.  For Example 3/2007 would

                    return the date 3/1/2007, 2007 would return 1/1/2007.  Allows users to enter as

                    text and come up with earliest date for that partial date.


                    USER INPUTS

                        TheDate = The Date in text format





                    Case (

                    Trim(TheDate) = "Unknown" or IsEmpty ( TheDate ); GetAsDate("");


                    PatternCount ( TheDate ; "/" ) = 0; GetAsDate ("1/1/" & TheDate);


                    PatternCount ( TheDate ; "/" ) = 1; GetAsDate ( Left ( TheDate ; Position ( TheDate ; "/" ; 1 ; 1 ) - 1 )  & "/1/" & Right ( TheDate ; Length ( TheDate ) - Position ( TheDate ; "/" ; 1 ; 1 )  ));


                    PatternCount ( TheDate ; "/" ) = 2; GetAsDate (TheDate);





                    I then have a calc field that I use for the date.  You could easily adapt the CF to apply your own partial date rules. In my situation I needed to keep the approximate or partial date such as 12/2011 yet have a real date of 12/1/2011.


                    Hope that helps.

                    • 7. Re: Interval calculating from approximate dates

                      Yes Ken,


                      This is very helpful.  With a bit of work, I think I can adapt this function to accommodate weekly, quarterly, or seasonal precisions, perhaps also decade and century precisions.


                      For some reason your response does not show a "Mark as Helpful" button.


                      Thank you,


                      • 8. Re: Interval calculating from approximate dates

                        For some reason your response does not show a "Mark as Helpful" button.

                        I would guess 4 pts is the total to be alloted for a question. If you have marked 2 people as helpful [2 pts each], there are no more points to alot. Too bad. I really liked Ken's reply.