Fields: 

ACTIVE = YES value 

DATE STARTED

DATE ENDED

gDATE_TODAY = global Get(Current_Date)

YEARS OF SERVICE (calculated result)

If the ACTIVE field is "yes" then I want to see current YEARS OF SERVICE  from today. Else, I want to see YEARS OF SERVICE  from start to end date.

Tried scenario that isn't quite working: 

If (Active="yes"; Year (Date_Today) - Year (Date_Started); Year (Date_Ended) - Year (Date_Started))

Edited November 16, 20223 yr by Raizel

Use a custom function https://www.briandunning.com/cf/518

GetValue (  Case ( Active = True  ; age ( DateStarted ; Get(CurrentDate) ; age ( DateStarted ; DateEnded  )  )  ; 4 ) 

25 years, 10 months, and 30 days

 

I entered the calculation however it does not take into consideration there maybe one date (start) so result needs to be from start date to today (gDATE_TODAY) (not sure if this field needs to be global)

I don't see why you need both the Active field and the Date_Ended field: after all, if someone is inactive, then they have a value in the Date_Ended field, do they not? 

If so, the calculation you started could be simply:

Let ( 
end = If ( Date_Ended ; Date_Ended ; Get ( CurrentDate ) )
;
Year ( end ) - Year ( Date_Started )
)

The result type is Number and the calculation field must be unstored.

However, simply subtracting the years is not a very precise calculation of age. A more accurate method would also include the fractional part of the (average) year:

Let ( 
end = If ( Date_Ended ; Date_Ended ; Get ( CurrentDate ) ) 
; 
( end - Date_Started ) / 365.2425 
)

You can round the result to desired precision or simply format the field to display a fixed number of decimals.

P.S. There is absolutely no need to have a global field with the current date. 

 

 

The ACTIVE field is a value of YES for more visual means of an active charity. I changed the calculation to the last shown and it's working rounded to one decimal to display. Removed the global field. Thanks

8 minutes ago, Raizel said:

The ACTIVE field is a value of YES for more visual means of an active charity.

This is not good practice. The entire idea of a relational database is to record each fact in one place only. You have plenty of ways to provide a visual indication of the status, based on the value (or absence thereof) in the Date_Ended field. For example, you could conditionally hide a text object.

 

With a calculation working and not requiring the ACTIVE button, will change.