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

--

Paul Spafford

FileMaker Database Superhero,

Spafford Data Solutions

Tel: 613 838 9956

www.paulspafford.com

www.ottawafilemakerdeveloper.com

www.fmlayoutmode.com

www.fmscripts.com

FileMaker User Group in Ottawa:

www.meetup.com/fudgeo

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.

Regards,

Ray

------------------------------------------------

R J Cologon, Ph.D.

FileMaker Certified Developer

Author, FileMaker Pro 10 Bible

NightWing Enterprises, Melbourne, Australia

http://www.nightwingenterprises.com

------------------------------------------------

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,

Ted.

• ###### 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.