Hi
can anyone tell me how to calculate the exact age of a person using the formula function?
I am using name:age
Year(Today()) - Year(LOC:DOB) which gives me the age at the next birthday and not the person’s current age.
Hope this makes sense
Many Thanks
use AGE built-in function
Message = Emp:Name & ‘is ’ & AGE(Emp:DOB,TODAY()) & ’ old today.’
The AGE function is ok, but it will only return an approximation.
From the docs
1 to 60 days - 'nn DAYS'
61 days to 24 months - 'nn MOS'
(2 years + 1 month) to 999 years - 'nnn YRS
So if I put a DOB of 1 Jan 1966 and used the current date as 1 Feb 2024, I would only get back the years. I guess you could do some trickery to parse the returned string and perform some date math to get a result of Years Months Day. But I think for that some calculation method would be better, taking into account leap years etc.
DaysOld = TODAY() - DateOfBirth
YearsOld = DaysOld / 365.25 ! this is an approximation
Tony, thanks!! 
This is what I use
YearDiff = YEAR(EndDate) - YEAR(StartDate)
DaysLastMonth = DAY(DATE(MONTH(EndDate),0,YEAR(StartDate)))
MonthDiff = MONTH(EndDate) - MONTH(StartDate)
DaysDiff = DAY(EndDate) - DAY(StartDate)
IF DaysDiff < 0
DaysDiff = DaysLastMonth + DaysDiff
MonthDiff = MonthDiff - 1
END
IF MonthDiff < 0
YearDiff = YearDiff - 1
MonthDiff = 12 + MonthDiff
END
I have a function I use in MSSQL to get age in years. It would be easy to adapt to Clarion code.
CREATE FUNCTION [dbo].[fnAgeAsOf] (@BirthDate DATE, @AsOfDate DATE)
RETURNS INT
AS
BEGIN
DECLARE @Age SMALLINT;
SET @age=DATEDIFF(yy, @birthdate,@asofdate);
SET @birthdate = DATEADD(yy, @age, @birthdate);
IF @birthdate>@AsOfDate
SET @Age=@Age-1;
RETURN @age;
END;
GO
(edited to include missing first line)
(post deleted by author)
I think that picture would not return a 0 on month or day?
(post deleted by author)
You meant about the effect of leap years, or people borned in february 29, or other thing?
The “quite eloquent” code is interesting, but wrong due to leap years (assumes all ages start in 2000) plus pictures not showing Zero Days and Months on the persons date.
The OP simply wants age in Years. Everyone knows on or after your Birthdate Month and Date you advance your age 1 year, before that date you do not. So keep it simple:
AgeInYears PROCEDURE(LONG DOB)!,SHORT
AgeNow SHORT,AUTO
YearNow SHORT,AUTO
DateThisYear LONG,AUTO
CODE
YearNow=Year(TODAY())
AgeNow =YearNow - Year(DOB)
DateThisYear = DATE(Month(DOB),DAY(DOB),YearNow)
IF TODAY() < DateThisYear THEN !Has not reached BDay of this year
AgeNow -= 1
END
RETURN AgeNow
The DATE() function can take a wrong date like DATE(2,29,2001) and adjust it to be 3/1/2001 so someone born on 2/29 advances on 3/1.
(post deleted by author)
Thanks so much.
Kind regards
YS = YEAR(Today()) - YEAR(BirthDate)
IF MONTH(Today()) >= MONTH(BirthDate)
MS = MONTH(Today())-MONTH(BirthDate)
ELSE
MS = ((MONTH(Today()) - MONTH(BirthDate))+12)%12
YS -= 1
.
IF DAY(Today()) >= DAY(BirthDate)
DS = DAY(Today())-DAY(BirthDate)
ELSE
DS = DATE(MONTH(BirthDate) +1,1,YEAR(BirthDate)) - BirthDate + DAY(Today()) - 1
IF MS
MS -= 1
ELSE
MS = 11
YS -= 1
.
.
An alternative to calling the Day() Month() Year() functions many times is to use a DATE type that has an internal format of YYYYMMDD which can be accessed with an OVER. Due to Little Endian its coded as DDMMYYYY. This is documented in the DATE Type Help.
AgeCalc PROCEDURE(LONG BirthDate)
B_DATE DATE !Internal Format YYYYMMDD but little Endian makes it DDMMYYYY
Birth GROUP, OVER(B_DATE)
Day BYTE
Month BYTE
Year USHORT
END
T_DATE DATE
Today LIKE(Birth),OVER(T_DATE)
CODE
B_DATE = BirthDate
T_DATE = TODAY()
YS = Today.Year - Birth.Year !was = YEAR(Today()) - YEAR(BirthDate)
IF Today.Month >= Birth.Month !was IF MONTH(Today()) >= MONTH(BirthDate)
MS = Today.Month-Birth.Month !was = ((MONTH(Today()) - MONTH(BirthDate))+12)%12
ELSE
MS = ((Today.Month - Birth.Month)+12)%12
YS -= 1
.
IF Today.Day >= Birth.Day
DS = Today.Day-Birth.Day
ELSE
DS = DATE(Birth.Month +1,1,Birth.Year) - BirthDate + Today.Day - 1
IF MS
MS -= 1
ELSE
MS = 11
YS -= 1
.
.
I didn’t test the above code at all. Note that 7 calls of TODAY() does involve a lot of code to get the system clock from the hardware every time and calculate date and time.
This technique is handy for TIME which does not have functions for Hour() Minute() Second() and is documented in the TIME type help.
Hi Tonymarshall,
You can try below logic to arrive the age for DOB and let me know if it works fine for you.
emp_age = 0
if emp_borndate > 0
emp_age = year(System_Date) - year(emp_borndate)
if month(System_Date) < month(emp_borndate)
emp_age = emp_age - 1
end
if month(System_Date) = month(emp_borndate)
if day(System_Date) < day(emp_borndate)
emp_age = emp_age - 1
end
end
end
Code looks good! You could be slightly more efficient with an ELSIF. There is no need for 2nd IF to test for “month equals” when you already know its “less than”.
emp_age = year(System_Date) - year(emp_borndate)
if month(System_Date) < month(emp_borndate)
emp_age = emp_age - 1
!Out--> end
ELSif month(System_Date) = month(emp_borndate) !<-- ELSIF was IF
if day(System_Date) < day(emp_borndate)
emp_age = emp_age - 1
end
end
A crazy thought trying to reduce code without adding variables:
if emp_borndate > 0
emp_age = year(System_Date) - year(emp_borndate)
CASE month(System_Date) - month(emp_borndate)
OF -11 TO -1 !Has not reached Birth Month this year
emp_age -= 1 ! e.g. Today in JAN, born in DEC = 1-12 = -11
OF 0 !Today Month = Birth Month
if day(System_Date) < day(emp_borndate) then
emp_age -= 1
end
END
end