Forum Discussion
Calculating age using 2 dates and then rounding down
I'm trying to calculate the age of people at the time they were diagnosed with a health condition. I have 2 dates, date of birth and date of diagnosis. I've managed to do this using DATEDIFF but some of the ages being returned are wrong. For example a person born on 1/12/1968 would have been 53 when they were diagnosed on 11/01/2022, however the DATEDIFF is returning an age of 54. Is there a way that the number returned is rounded down to reflect the actual age at the time of being diagnosed? Thanks.
The reason DATEDIFF isn't working is because it counts the number of boundaries which have been crossed between the two dates, its not working to a particular number of days. A DATEDIFF between 31st Dec and 1st Jan gives 1 year, as 1 boundary has been crossed.
You could try instead using
Age = YEAR ( 'Table'[Date of diagnosis] - 'Table'[Date of birth] ) - 1900pls try this
4 Replies
johnt75Super User
The reason DATEDIFF isn't working is because it counts the number of boundaries which have been crossed between the two dates, its not working to a particular number of days. A DATEDIFF between 31st Dec and 1st Jan gives 1 year, as 1 boundary has been crossed.
You could try instead using
Age = YEAR ( 'Table'[Date of diagnosis] - 'Table'[Date of birth] ) - 1900That has worked. Thanks very much Johnt75.
- Ahmedx
pls try this
That seems to have worked! Thanks very much Ahmedx.
