Method 1: Use DATEDIF to Calculate Age in Years, Months & Days Step 1: Calculate the number of complete years years = DATEDIF( start_date, end_date, "y") Step 2: Calculate the remaining number of complete months after subtracting years from the end date. The point is that there may be multiple right answers, depending on the methodology. If we assume a 30-day month, then ( end_date-30) results in. However, if we use the DATE( year, month-1, day) method, the result would be. The complications and differences come from how we treat months with different numbers of days in them.įor example, if you subtract a month from 3, should it be 2? If so, we can use EDATE( end_date,-1) or EOMONTH( end_date,-1). There are multiple methods for calculating the age as a combination of years, months and days, but not all methods give the same answers. Calculate Age in Years, Months and DaysĪges are often represented using the format 5y 11m 3d or 5 years 11 months and 3 days. Though the error is rare (< 1% of date combinations I tested), this method is not a valid substitute for DATEDIF(start,end,"y"). Using INT or ROUNDDOWN with this method to return the number of whole years between two dates will sometimes lead to incorrect results (e.g. DATEDIF automatically uses DATEVALUE to convert valid date text to date values, so DATEDIF("1","","y") would work. Although dates cannot be earlier than 1900, they can either be date values or text. This is the simplest and most accurate method. Remaining number of days after Adding whole years and months to the Start DateĬalculate Age in Years Method 1: Use DATEDIF to Return the Number of Whole Years Between Two Dates Remaining number of days after ignoring complete years Remaining number of whole months (derived from "y" and "m") Number of complete months between two dates Number of complete years between two dates DATEDIF "md" bug and the work-around that worksĭATEDIF( start_date, end_date, interval) Interval.Age in years, months and days (2 methods).
0 Comments
Leave a Reply. |