Interval in YMD between two dates

[Art]

I'm trying to calculate in years, months, and days how long it is between a date on a past record in FileMaker to the current date.

I have a calculation field that I thought was working perfectly . . . until I got to 1/1/12 and then the calculation starting going backward.

The calculation in the calc field is:

GetAsText ( Year ( Get ( CurrentDate ) ) - Year ( SermonDate ) - If ( Get ( CurrentDate ) < Date ( Month ( SermonDate ) ; Day ( SermonDate ) ; Year ( Get ( CurrentDate ) ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( Month ( Get ( CurrentDate ) ) - Month ( SermonDate ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( SermonDate ) ; 1 ; 0 ) ; 12 ) ) & " Months, " & GetAsText ( Day ( Get ( CurrentDate ) ) - Day ( SermonDate ) + If ( Day ( Get ( CurrentDate ) ) ≥ Day ( SermonDate ); 0 ; If ( Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) < Day ( SermonDate ) ; Day ( SermonDate ) ; Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) ) ) ) & " Days "

I have a date field that is labeled "SermonDate" and as I said, the above calculation seemed to be working until the beginning of this year. I've tried to find the flaw but I can't. Anyone have an idea what's wrong?

Thanks for any help given.

[comment]

Oh my... see if this can get you started (or discouraged):

http://fmforums.com/...-business-calc/

---

BTW, what is the date of the sermon that started going backward?

Edited January 17, 2012 by comment

[LaRetta]

You need to include a test first that 1) the SermonDate is not empty and 2) the SermonDate is less than or equal to the current date. Otherwise you will get results such as: -2 years, 3 months, 2 days

I believe that is what isi meant by 'going backwards'.

ADDED: Many Users forget to change a year backwards when data-entering. If they depend upon entering 6/15 and it be 6/15/2011, it will instead be 6/15/2012 - thus your break. Maybe that is what you are seeing suddenly when the year changes?

[Art]

Thanks for the suggestions above. For some reason I haven't gotten any notification that anyone left a question or solution to my question on this forum. And to answer the question above as to what date the sermon started going backwards, it was January 1, 2012 (right! this year). And to answer the other question raised, I was using the Get current date as the date from which to calculate. And every record in the file was formed by getting the last record's date field and adding 7 to that date. So the date was entered by formula not by manually entering the date into the record.

But, . . . I've finally gotten an answer that correctly calculates. For anyone interested and who is trying to do this . . . this is pretty cool . . . check out this link.

http://forums.filema...osts/ab014543f7

Art

[LaRetta]

But anyway to move on before Michael sees ... :jester:

[LaRetta]

Well, the bat is out of the cag so ... although I earlier just stayed quietly in the background like a good half-blind cat, I now must speak up because I realize I had a flaw in those calcs where it would sometimes break. I thought I had fixed that post.

Change where theDate is your birth date and today is Get ( CurrentDate ) or reference date

All shown, 0 years, 3 months, 0 days

Case ( theDate and Today and theDate ≤ Today ;

Let ( [

next = Date ( Month ( theDate ) + 1 ; Day ( theDate ) ; Year ( theDate ) ) ;

end = Today;

startlen = Day ( Date ( Month ( theDate ) + 1 ; 0 ; Year ( theDate) ) ) ;

x = 12 * ( Year ( end ) - Year ( theDate ) ) + Month ( end ) - Month ( theDate ) - ( Day ( end ) < Day ( theDate ) ) ;

y = Div ( x ; 12 ) ;

m = Mod ( x ; 12 )  ;

d = Case ( Day ( theDate ) > Day ( end )  ; startlen +  Day ( end )  - Day ( theDate)  ; Day ( end ) - Day ( theDate) )

] ;

y & " year" & Case ( y  ≠ 1 ; "s" )  & ", " &

m & " month" & Case ( m  ≠ 1 ; "s" ) & ", " &

d & " day" & Case ( d  ≠ 1 ; "s" )

) // END LET

) // END CASE

Limited shown, 3 months, 1 day

Case ( theDate and Today and theDate ≤ Today ;

Let ( [

next = Date ( Month ( theDate ) + 1 ; Day ( theDate ) ; Year ( theDate ) ) ;

end = Today;

startlen = Day ( Date ( Month ( theDate ) + 1 ; 0 ; Year ( theDate) ) ) ;

x = 12 * ( Year ( end ) - Year ( theDate ) ) + Month ( end ) - Month ( theDate ) - ( Day ( end ) < Day ( theDate ) ) ;

y = Div ( x ; 12 ) ;

m = Mod ( x ; 12 )  ;

d = Case ( Day ( theDate ) > Day ( end )  ; startlen +  Day ( end )  - Day ( theDate)  ; Day ( end ) - Day ( theDate) )  ;

string =

Case ( y ; y & " year" & Case ( y  ≠ 1 ; "s" ) & ", " ) &

Case ( m ; m & " month" & Case ( m  ≠ 1 ; "s" ) & ", " ) &

Case ( d > 0 ; d & " day" & Case ( d  ≠ 1 ; "s" ) )

] ;

LeftWords ( string ; Length ( string ) )

) // END LET

) // END CASE

I keep coming back to this issue of trying to perfect this calculation even though I know there is no such thing as a right answer. And I believe that we can draw 'human' conclusions and as long as the results are understood to be subjective, this has less 'faults' than the others that I have tested.

Sometimes I get a weak moment and end up responding to it. I've been feeling pretty good lately. I hope it isn't time to get smacked down for trying. I can be weak-willed when it comes to date calculations. :laugh: