You shouldn't have to create a separate field for Today - you can use the Get ( CurrentDate ) function instead in the end calculation. Make sure that any calculation it is used in is set to be 'Unstored'.
A rough go at working out the years, months, and days between two dates, Date1 and Date 2 (in your case it looks like Date2 would be Get ( CurrentDate ) ) is:
DayOfYear ( Date2 ) ≥ DayOfYear ( Date1 ) ; Year ( Date2 ) - Year ( Date1 ) ;
Year ( Date2 ) - Year ( Date1 ) - 1
Month ( Date2 ) ≥ Month ( Date1 ) and Day (Date2 ) ≥ Day ( Date1 ) ;
Month ( Date2 ) - Month ( Date1 ) ;
Month ( Date2 ) ≥ Month ( Date1 ) and Day (Date2 ) < Day ( Date1 ) ;
Month ( Date2 ) - Month ( Date1 ) - 1 ;
Month ( Date2 ) < Month ( Date1 ) and Day (Date2 ) ≥ Day ( Date1 ) ;
12 - Month ( Date1 ) + Month ( Date2 ) ;
12 - Month ( Date1 ) + Month ( Date2 ) - 1
Day ( Date2 ) ≥ Day ( Date1 ) ; Day ( Date2 ) - Day ( Date1 ) ;
DayOfYear ( Date ( Month ( Date2 ) ; "1" ; Year ( Date2 ) ) )
DayOfYear ( Date ( Month ( Date1 ) ; Day ( Date1 ) ; Year ( Date2 ) ) )
Note that I haven't checked for Date2 being *before* Date1, for example. And no, I haven't fully tested this, but it should get you going on the right lines.
The eassiest way to calculate the days between two date is: Date1 - Date2 .... the result is the number of days.
If you wish that to see a a kind of date, add getasdate() to the formula: getasdate( Date1 - Date2 )
The latter formula has a few flaws because it projects the outcome of the first formula on the beginning of the gregorian calander. The correct total lenght of months causes possibly a shift between -3 and +3 days. If that's a problem you should calculate more exact with:
Date ( md ; dd ; yd ) )
(Neat behaviour by the date-function is that an input like: Date ( 5 ; -1 ; 2011 ) will put out: april 30, 2011)
If you wish to display it as:
6 years, 3 months, 3 days or 2 months, 1 day then you can try this calculation (result is text), set to unstored:
Let ( [
start = YourDate ;
next = Date ( Month ( start ) + 1 ; Day ( start ) ; Year ( start ) ) ;
end = Get ( CurrentDate ) ;
x = 12 * ( Year ( end ) - Year ( start ) ) + Month ( end ) - Month ( start ) - ( Day ( end ) < Day ( start ) ) ;
y = Div ( x ; 12 ) ;
m = Mod ( x ; 12 ) ;
d = Case ( Day ( start ) > Day ( end ) ; Day ( next - Day ( next ) ) + Day ( end ) - Day ( start ) ; Day ( end ) - Day ( start ) ) ;
string = Case ( y ; y & " year" & Case ( y > 1 ; "s" ) & ", " ) & Case ( m ; m & " month" & Case ( m > 1 ; "s" ) & ", " ) & Case ( d ; d & " day" & Case ( d > 1 ; "s" ) )
LeftWords ( string ; Length ( string ) )
) // END LET
Substitute 'YourDate' for hire date. And delete the field Today using instead Get ( CurrentDate ) .
It worked great. The only problem is it shows in the record as 564.00. But when I click with the mouse in the field then it will show as 5 years, 6 months, 4 days. So the calculation and the information is correct, I just need to find out a way to let it display the results without having to click in the field.
I got it . I formatted the appearance of the field in inspector to general. It used to be decimal and every thing is just perfect. Thanks again