If the end date is, the result will end up being -2. It uses DATE(2015,1+1,31) which returns the date. Why does DATEDIF return -1 or -2? Here is an example: When adding a month to 3, if we used EDATE("3",1), we'd get 2 which is what you might be expecting, but DATEDIF does not use EDATE. = end - DATE( YEAR( start), MONTH( start) + DATEDIF( start, end,"m"), DAY( start)) The following formula matches the result of DATEDIF(start,end,"md") 100% of the time (based on extensive random sampling of dates), so I'm pretty sure that this is how DATEDIF is doing the calculation. Method #2 is a valid method that never results in negative values and fits the definition of "remaining days between dates after subtracting whole months." Method 1: Return remaining days after Adding whole months to the Start Date Method #1 is the method I think DATEDIF uses, but it can lead to negative values that are clearly wrong. There are two methods for calculating the remaining days, shown below. The work-around formula recommended by Microsoft on their support page is wrong even more often. DATEDIF "md" Bug and Work-AroundĪs I mentioned above, using the "md" unit in the DATEDIF function is not always correct (both in Excel and in Google Sheets). =VALUE(RIGHT(INT( yymmdd/100),2)) to return the months and use =VALUE(RIGHT( yymmdd,2)) to return the days, use You can use the following custom number format for YYMMDD age values to only display the years and months if they are not zero: 0"y "00"m "00"d" 0"m "00"d" 0"d" To convert a date value (>1900) to a YYYYMMDD value, use =VALUE(YEAR( date) & TEXT(MONTH( date),"00") & TEXT(DAY( date),"00")) Method 1: Add 2000 years then use the formulas aboveįor example, use the formula below to calculate the age when the birth date is 2 and death date is 2. Below are a couple of methods for calculating age in Excel when working with dates prior to 1900. If you enter a date such as 2 in Excel, it will store that as the number 42975, but if you enter a date prior to 1900, it will store the date as text.
#EXCEL ROUND A DATE UP TO THE FIRST OF THE MONTH SERIAL NUMBERS#
It turns out that if you are okay with the DATEDIF "md" option leading to negative values for days, and use DATEDIF(start,end,"md") in place of Step #3, this formula will return the original end date 100% of the time.Ĭalculate Age in Excel for Dates Prior to 1900Įxcel Date functions only work for dates after, because the date serial numbers in Excel begin with 1 on. If you use Method 1 above to calculate the age, this formula will return the original end date about 75% of the time (can be off by up to 3 days). days = DATE( YEAR( end), MONTH( end) - DATEDIF( start, end,"m"), DAY( end)) - start months = DATEDIF( start_date, end_date, "ym") Step 3: Calculate the remaining number of days after subtracting months from the end date. 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 kommentar(er)
