Whether the answer is correct depends upon the methodology used to calculate the age.Other methods, such as INT((end-start)365.25) or INT(YEARFRAC(start,end)) are not 100 correct.
See below for examples and an explanation of other formulas having to do with calculating age in Excel. Although dates cannot be earlier than 1900, they can either be date values or text. DATEDIF automatically uses DATEVALUE to convert valid date text to date values, so DATEDIF(17-Aug-1968,2017-08-17,y) would work. This template can be used to show the birthdays of your relatives on a yearly calendar, and the DATEDIF function calculates what their age will be on their birthday. It returns the same results if the inputs are valid dates after 1900 and end start. How it works: It subtracts the years and then subtracts 1 if the month and day of the end date comes before the month and day of the start date. To calculate age using this method, first subtract the dates to calculate the number of days, then divide by 365.2422. There are multiple methods for calculating the age as a combination of years, months and days, but not all methods give the same answers. The complications and differences come from how we treat months with different numbers of days in them. However, if we use the DATE( year, month -1, day ) method, the result would be 3-Mar-2017. If we assume a 30-day month, then ( enddate -30) results in 1-Mar-2017. The point is that there may be multiple right answers, depending on the methodology. The work-around formula end -DATE(YEAR( end ),MONTH( end,1) listed on the Microsoft Support site here and here is not correct (e.g. Jan-2016 to 31-Jan-2017 returns the value 30 and it should be 0). This fact makes it a very useful methodology because the process is reversible (information is not lost). In elementary school, you learned that when subtracting you can borrow from the tens or hundreds place when needed. Here, if we need to borrow from the MM place, we subtract one from MM and then add 30 days to DD. If we need to borrow from the YYYY place, we subtract one from the YYYY place and add 12 to MM. Excel may try to convert the result to a date, so change the number format back to General. How it works: Start with the month of the end date, then subtract 1 if you had to borrow 30 days, then add 12 months if you need to borrow from the years, then subtract the month of the start date. How it works: Start with the year of the end date, then subtract 1 if you previously had to borrow 12 months, then subtract the year of the start date. Turns out that both methods return the exact same number of years and months.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |