How to Calculate the Number of Years Between Two Dates

How to count the number of Years between two dates

Just use the =DAYS(end_date,start_date) formula, and then divide by 365 to get the number of years; no need to use VBA. If you are doing it as part of a larger VBA macro, you can just use WorksheetFunction.Days(end_date,start_date).

EDIT: Even easier: just use =YEARFRAC(). It'll do it all for you.

You should note that simply dividing by 365 does not account for leap years, which introduces some error into the answer. While this error is trivial in this case, you may avoid it simply by using =YEARFRAC(start_date, end_date, [basis]), and using 1 as the basis parameter. This will force the use of actual calendar days, which will account for leap year correctly. Otherwise, YEARFRAC assumes a standard 30-day month for every month, and your answer will be increasingly inexact.

How can I calculate the number of years between two dates?

Probably not the answer you're looking for, but at 2.6kb, I would not try to reinvent the wheel and I'd use something like moment.js. Does not have any dependencies.

The diff method is probably what you want: http://momentjs.com/docs/#/displaying/difference/

Calculating number of years between two dates, but rounded in the standard way

I found a solution that is compatible with leap years. It's still not as clean as I'd like, but it does the job:

from datetime import datetime, date
from dateutil.relativedelta import relativedelta

def how_many_years_ago(input_date):
    difference = relativedelta(date.today(), input_date)
    result = difference.years

    # Round up if closer to the next year than the previous
    if difference.months >= 6:
        result += 1

    return result

# Exactly 1 year ago, should output 1
print(how_many_years_ago(date.today() - relativedelta(years=1)))

# Almost 1 year ago, should round up to 1
print(how_many_years_ago(date.today() - relativedelta(years=1) + relativedelta(days=1)))

# Too far removed from 35 years, should round down to 34
print(how_many_years_ago(date.today() - relativedelta(years=35) + relativedelta(months=8)))

# Almost 38 years ago, should round up to 38
print(how_many_years_ago(date.today() - relativedelta(years=38) + relativedelta(months=4)))

If I'm not overlooking certain caveats (like leap years), this implementation solves my problem.

How to calculate number of years between two dates in different pandas columns

I believe need:

df['age'] = (np.floor((pd.to_datetime(df['dte']) - 
             pd.to_datetime(dob)).dt.days / 365.25)).astype(int)
print (df)
           id         dte  age
0  19520630F8  2010-06-02   57
1  19680321A5  2007-08-12   39
2  19711113E2  2013-01-23   41

Details:

Convert columns to datetimes and subtract:

print (pd.to_datetime(df['dte']) -  pd.to_datetime(dob))
0   21156 days
1   14388 days
2   15047 days
dtype: timedelta64[ns]

Convert to days and then to years:

print ((pd.to_datetime(df['dte']) -  pd.to_datetime(dob)).dt.days / 365.25)
0    57.921971
1    39.392197
2    41.196441
dtype: float64

Last floor values by numpy.floor.:

print ((np.floor((pd.to_datetime(df['dte']) - pd.to_datetime(dob)).dt.days / 365.25)))
0    57.0
1    39.0
2    41.0
dtype: float64

Calculating the number of dates in specific year, between two dates

You didn't specify your dbms, but in general terms:

• Get the 1st day of the @EndDate year i.e. January 1, 2017
• Then calculate the days difference between @FirstOfYear and @EndDate

For example in MySQL, you could use MAKEDATE() to get the first of the year. Then DATEDIFF() to calculate the number of days

SET @StartDate = '2016-12-30';
SET @EndDate = '2017-01-05';  

SELECT DateDiff(@endDate, MAKEDATE(Year(@endDate),1)) + 1 AS DaysInEndDateYear

Result:

| DaysDiffValue|
| ----------------: |
|                 5 |

If you also need to handle ranges where both @StartDate and @EndDate are in the same year:

SET @StartDate = '2017-01-05';
SET @EndDate = '2017-03-14';  

SELECT CASE WHEN Year(@StartDate) = Year(@EndDate) THEN DateDiff(@EndDate, @StartDate) + 1
            ELSE DateDiff(@endDate, MAKEDATE(Year(@endDate),1)) + 1
       END AS DaysInEndDateYear

Results:

| DaysInEndDateYear |
| ----------------: |
|                69 |

db<>fiddle here

How can I find the number of years between two dates?

import java.util.Calendar;
import java.util.Locale;
import static java.util.Calendar.*;
import java.util.Date;

public static int getDiffYears(Date first, Date last) {
    Calendar a = getCalendar(first);
    Calendar b = getCalendar(last);
    int diff = b.get(YEAR) - a.get(YEAR);
    if (a.get(MONTH) > b.get(MONTH) || 
        (a.get(MONTH) == b.get(MONTH) && a.get(DATE) > b.get(DATE))) {
        diff--;
    }
    return diff;
}

public static Calendar getCalendar(Date date) {
    Calendar cal = Calendar.getInstance(Locale.US);
    cal.setTime(date);
    return cal;
}

Note: as Ole V.V. noticed, this won't work with dates before Christ due how Calendar works.

---

Related Topics