MorganNews
Technical User
I've spent fruitless hours on this one.
The problem: I want to produce a calculated text statement that says a specific date of an event that I enter is, only for example, 5 years, 4 months and 21 days from another specific future date.
The details: The specific future date, the end of the countdown, is Feb 12, 2010, and the entry date is some random date closer to now than that. Let's say the entry date is Feb 11, 2004. I want Filemaker to calculate the years, months and days between the two.
If I subtract using the date function to get the answer in years, for instance, I get 6, which is correct. However, if the entry date is Feb 13, 2004, I also get 6 years, which is (for my purposes) incorrect, because the correct answer is that Feb 13, 2004 is 5 years, 11 months and (don't forget the leap year)16 days from Feb 12, 2010.
I have tried innumerable but unsuccessful variations on the theme of:
(Date(2,12,2010)-Date(Day(Entry Date),Month(Entry Date),Year(Entry Date) as well as variations of, say: month(Date(2,12,2010)-month(Entry Date).
But usually, the years, months or days are wrong for my purposes. Sometimes they are correct for dates on one side of the anniversarys of the countdown date, but not on the other. In the example above, the calculation for the years is correct only up to Feb 12 of any given year, and then it's wrong. Sometimes I get minus numbers for, say, the days or the months, which would also be incorrect. So, I'm also having problems getting the correct answer for months and days from the 2010 date that's correct in all instances.
The question: How do I get the answer that's correct for what I'm trying to achieve?
The problem: I want to produce a calculated text statement that says a specific date of an event that I enter is, only for example, 5 years, 4 months and 21 days from another specific future date.
The details: The specific future date, the end of the countdown, is Feb 12, 2010, and the entry date is some random date closer to now than that. Let's say the entry date is Feb 11, 2004. I want Filemaker to calculate the years, months and days between the two.
If I subtract using the date function to get the answer in years, for instance, I get 6, which is correct. However, if the entry date is Feb 13, 2004, I also get 6 years, which is (for my purposes) incorrect, because the correct answer is that Feb 13, 2004 is 5 years, 11 months and (don't forget the leap year)16 days from Feb 12, 2010.
I have tried innumerable but unsuccessful variations on the theme of:
(Date(2,12,2010)-Date(Day(Entry Date),Month(Entry Date),Year(Entry Date) as well as variations of, say: month(Date(2,12,2010)-month(Entry Date).
But usually, the years, months or days are wrong for my purposes. Sometimes they are correct for dates on one side of the anniversarys of the countdown date, but not on the other. In the example above, the calculation for the years is correct only up to Feb 12 of any given year, and then it's wrong. Sometimes I get minus numbers for, say, the days or the months, which would also be incorrect. So, I'm also having problems getting the correct answer for months and days from the 2010 date that's correct in all instances.
The question: How do I get the answer that's correct for what I'm trying to achieve?
