Calculate Year and Month Between Two Dates

[Chris Waller]

I have some date ranges in column A i.e.06/04/50 - 05/05/50 and in column B a date in the following format dd/mm/yyyy. In Column C, I want to calculate the years and months from column B to the first date in column A. In Column D, I want to calculate the years and months from Column B to the second date in column A. I hope I have explained what I am trying to do clearly and I think the answer may include parts if not all of the following formulas, but trying to combine the two formulas is proving troublesome. TIA

=MID(A2,1,8)

=DATEDIF(A1621,B1621,"y")&" years, "&DATEDIF(A1621,B1621,"ym")&" months"

 

[BrianMH]

Can you post an example of the data and the result you want? Use the addin at Excel Jeanie Html to get table code for the forums.

 

[energman58]

=DATEDIF(MIN(DATEVALUE(LEFT(A20,FIND("-",A20)-2)),DATEVALUE(MID(A20,FIND("-",A20)+2,9))),B20,"Y")

is the formula for column C - same in COlumn D but swap the min for a max (you obviously have to add the bit for the months too but I wasn't going to type all that!!!)

HTH

 

[Chris Waller]

Can you post an example of the data and the result you want? Use the addin at Excel Jeanie Html to get table code for the forums.

		Calculation using 1st Date	Calculation using 2nd Date
06/04/1950 - 05/05/50	06/05/2010	60 years, 1 months	60 years, 0 months

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[BrianMH]

Excel Workbook
	A	B	C	D
1	06/04/1950 - 05/05/50	06/05/2010	60 Years , 1 Months	60 Years , 0 Months
Sheet1

 

[Chris Waller]

energman,

Your post was helpful, however I am only a novice at this and do not have the knowledge to be able to modify it to include the years and months. I have however, managed to change the min to max, but that is as far as I can go. TIA

 

[energman58]

Your first post was well on the right track so I assumed you could take it from there. However here is the full version:

In C

=DATEDIF(MIN(DATEVALUE(LEFT(A20,FIND("-",A20)-2)),DATEVALUE(MID(A20,FIND("-",A20)+2,9))),B20,"Y")&" Years " & DATEDIF(MIN(DATEVALUE(LEFT(A20,FIND("-",A20)-2)),DATEVALUE(MID(A20,FIND("-",A20)+2,9))),B20,"YM")&" Months"

In D change the two mins for max

Job done!