Find year and month between two dates in Excel?

Question

I want to take today's date and subtract another date from it to find how long a person has been in a program. For example, today is Dec 27, 2006. The person started a program Feb 4, 2005. I want to show that the person has been in the program for 1 year 10 months...... can I do that in the same cell? How would I figure this out?

Okay what if I seperate years and months? Then could I do it? How would I do it then?

I've seen the "Datedif" function - but I have changing numbers - I have a list of dates and I basically want to subtract the first date from TODAY() and total how many years and how many months. I can do two seperate columns...... but the information changes, so I don't want to do a seperate equation for each set of dates.....

OKay so what I finally ended up doing was seperating years and months and then I did YEAR(A2) - YEAR(A1) in one column and MONTH(A2) - MONTH(A1) in the second column.

Answer 1

You can use
=date(yyyy2,mm2,dd2) - date(yyyy1,mm1,dd1)
to get the number of days. Number of years and months are difficult due to the varying number of days in a year and month.

Answer 2

The DAYS360 will give you a value for the number of days based on twelve 30-day periods. If you want EXACT number of days, you can just subtract the two.

As someone else mentioned, putting the years and months within one cell is difficult. If you were willing to use two or more cells, here is how I did it:

A2=Start Date
B2=End Date or TODAY()

# To calculate the days between the two dates
C2=B2-A2

# to calculate the number of years only
D2=ROUNDDOWN(C2/365,0)

# to calculate the number of months remaining after the year is removed
E2=ROUNDDOWN(((C2-
(D2*365))/31),0)


Keep in mind that this will only give a rough idea of how much time has elapsed between the dates. Because I did not factor in leap years and made an assumption that there were always 31 days in a month, the calculation could be off by as much as a month. But I doubt even over a 30 year period it would go off by much more than that. Just do not try to calculate days remaining afterwards because your days will likely be way off.

I used FEB 04, 2005 and DEC 27, 2006 in my A2 and B2 cells and came up with 1 year and 10 months.

You may also want to check out the YEARFRAC function in one of the analysis add-ins.

Answer 3

If A1 has the starting date and B1 has the ending date, try this formula:
=TEXT(INT((B1-A1)/365),"###") &" year(s) and "&TEXT(((B1- A1)/365-INT ((B1-A1)/365))*12, "##.#")&" month(s)"



The solution you've used won't work! As an example, if you have a starting date of 3/1/06 and an ending date of 1/1/08, your solution gives 2 years and -2 months. Try my formula. It produces "1 year(s) and 10.1 month(s)" as an answer, but you could easily format it differently.

Answer 4

Have you looked at the built in function DAYS360 ?