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.

[TABLE="width: 584"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD] Calculation using 1st Date
[/TD]
[TD]Calculation using 2nd Date[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06/04/1950 - 05/05/50[/TD]
[TD="align: right"]06/05/2010[/TD]
[TD] 60 years, 1 months
[/TD]
[TD]60 years, 0 months[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!