Solved

Calculating Age from Two Dates

Posted on 2006-07-10
10
976 Views
Last Modified: 2012-05-05
Very new, as in 2  weeks, to Filemaker and trying to clean up anothers mess. I need to perform a variety of date calculations, which all boil down to subtracting two dates from each other and reconstituting the difference back into years & months. Thus subtracting 1/1/2000 from 7/1/2006 would give me something on the order of "6 years 6 months" (or 6.6 which would be acceptable). I would not want the resulting answer to exceed two digits for years and 2 digits for months, so a resulting calculation of 10.11 (ten years eleven months) would be acceptable, but 10.111111111 (ten years ad nausem) would not.

Thanks,
Bob
0
Comment
Question by:Bob_Gardner
  • 5
  • 5
10 Comments
 
LVL 3

Expert Comment

by:Edoshin
ID: 17078920
The main problem with date math are extreme samples. For example, how would you like to round the incomplete months? Could you please tell how the formula you're looking for must behave in the following cases?

March 1, 2006..March 20, 2006 = 0.1? or 0.0?

March 30, 2006..Apr 30, 2006 = 0.1? or 0.0?
0
 

Author Comment

by:Bob_Gardner
ID: 17079419
Edoshin
 Thanks for the response. Incomplete months would be 0.

  As for your specifc examples,

 March 1, 2006..March 20, 2006 = 0.1? or 0.0? -> Would be 0.0

 March 30, 2006..Apr 30, 2006 = 0.1? or 0.0? -> would most be 0.1, given 30 days in April and 31 in March.
0
 
LVL 3

Expert Comment

by:Edoshin
ID: 17079623
I forgot yet another case: Apr 30...May 30. Should it be 0.0, because the end of March isn't reached yet or 0.1, because it seems natural. Anyway, here's a sketch of the formula:

Let( [
 date 1 = <your field>;
 date 2 = <your other field>;
 month 1 = Month( date 1 ); day 1 = Day( date 1 ); year 1 = Year( date 1 );
 month 2 = Month( date 2 ); day 2 = Day( date 2 ); year 2 = Year( date 2 );
 incomplete month =
   date 2 < If( Month( Date( month 2; day 1; year 2 ) ) = month 2;
    Date( month 2; day 1; year 2 );
    Date( month 2 + 1; 0; year 2 ) );
  number of months =
    ( year 2 - year 1 ) * 12 + ( month 2 - month 1 ) - Case( incomplete month; 1 ) ];

  Div( number of months; 12 ) & "." & Mod( number of months; 12 ) )

It calculates Apr 30..May 30 as "0.1"
0
 

Author Comment

by:Bob_Gardner
ID: 17083207
Edoshin
 I think there is a problem brought on by the fact that I failed to specify that the FM version being used is 6.04. That said, unless there is a typo in your formula, there is no "Let" or "Div" functions. I have included my rendition of your formula, which I was going to test, but can not get FM to accept it in the calculation portion of the field definition.

Let([
date1=Today;
date2=Date of Birth;
month1=Month( date1); day1=Day(date1); year1=Year(date1);
month2=Month(date2); day2=Day(date2); year2=Year(date2);
incomplete month = date2< If(Month(Date(month2; day1; year2))=month2;
Date(month2; day1; year2);
Date(month2 + 1; 0; year2));
number of months=(year2-year1) * 12 + (month2-month1)-Case(incomplete month; 1)];
Div(number of months; 12) & "." & Mod(number of months; 12))
0
 
LVL 3

Accepted Solution

by:
Edoshin earned 500 total points
ID: 17087815
Then the formula will be way longer and hardly readable :)

Int( ( ( Year( Today) - Year( Date of Birth ) ) * 12
+ ( Month( Today) - Month( Date of Birth ) )
- ( Today< If( Month( Date( Month( Today), Day( Date of Birth ), Year( Today) ) ) = Month( Today),
  Date( Month( Today), Day( Date of Birth ), Year( Today) ),
  Date( Month( Today) + 1, 0, Year( Today) ) ) ) ) / 12 )
& "."
& Mod( ( ( Year( Today) - Year( Date of Birth ) ) * 12
+ ( Month( Today) - Month( Date of Birth ) )
- ( Today< If( Month( Date( Month( Today), Day( Date of Birth ), Year( Today) ) ) = Month( Today),
  Date( Month( Today), Day( Date of Birth ), Year( Today) ),
  Date( Month( Today) + 1, 0, Year( Today) ) ) ) ), 12 )

Also, I see you use the Today function, which is typically avoided by FileMaker developers and has been completely removed in v7. It can be used, but under quite specific circumstances and since you work with FileMaker for short time, you probably don't know its specifics. Would you like me to explain what's wrong with it?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Bob_Gardner
ID: 17088456
Edoshin ,

Thanks - I'll try you latest code in the morning - I'm in Seattle and its a little after midnight. By the way, after I posted that bit, and before I got your latest response, I read a short note on the 'today' problem, with the recommendation that Status(CurrentDate) be used. I am now using that - I hope that is correct. I understood that 'today' is very, very slow - amonst other issues. As an aside, I do NOT like FM (at least not what I have seen so far) and being a child at it is very frustrating. More later.
Bob
PS If this works out - I'm upping the points to 500. I sincerely appreciate your assistance.
0
 

Author Comment

by:Bob_Gardner
ID: 17096876
Edoshin,
Hmmm .. there seems to be a slight problem. I had the field define as a number, which resulted in 18.5.6, and then when I changed the field to text, it resulted in 18.4111 (not using the same set of dates in both tests). I am not sure what problem may be causing these results. Any ideas?
0
 
LVL 3

Expert Comment

by:Edoshin
ID: 17096932
Something is wrong with the formula. Try this sample file: http://www.mytempdir.com/800622
0
 

Author Comment

by:Bob_Gardner
ID: 17100234
Edoshin,

 That worked! Great! Thanks lots.
Bob
0
 
LVL 3

Expert Comment

by:Edoshin
ID: 17101575
You're welcome :) Maybe you'll eventually like FileMaker :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now