Number of Months from....

[StuartM1]

Hi folks,

I do NOT have the DATEDIF function on my Excel. I'm not sure why. Anyway, how can I count the number of months as follows?

2016/11 vs. 2017/03 = 4

Any ideas??

Thanks very much!!!!!

 

[StuartM1]

Ok. So using falcondude's example, I think what im doing wrong is

A1 = "2016/11" typed in. It is Text.
B1 = a date in the custom format of yyyy/mm.

the result is #Value. So how can I calculate a fixed 2016/11 vs a 3/5/2017 converted to yyyy/mm ??

I appreciate all the help really!

 

[Dave Patton]

Excel 2010
	A	B	C	D
1	2016/11	201703		4
2	2016-11	201703		4
3	2016-11	201703		4
4
2a

Cell Formulas
Range	Formula
D1	=DATEDIF(DATE(LEFT(A1,4),RIGHT(A1,2),1),B1,"m")
D2	=DATEDIF(A2&"-1",B2,"m")
D3	=DATEDIF((A3&"-1")+0,B3,"m")

 

[63falcondude]

Ok. So using falcondude's example, I think what im doing wrong is

A1 = "2016/11" typed in. It is Text.
B1 = a date in the custom format of yyyy/mm.

the result is #Value. So how can I calculate a fixed 2016/11 vs a 3/5/2017 converted to yyyy/mm ??

I appreciate all the help really!

=DATEDIF(DATEVALUE(A1&"/1"),B1,"M")

or

=ROUND((EOMONTH(B1,-1)+1)-DATEVALUE(A1&"/1"))/30,0)

 

[StuartM1]

Awesome. Thanks everyone, very much!

 

[63falcondude]

Awesome. Thanks everyone, very much!

You're welcome. Glad to help.

 

[StuartM1]

Hi Falcon, Dave and snjpverma.

I got it to work until the full date became a calculation. Could you please look at the image below. I get only #VALUE! in Col E. I'm trying to count the number of months from cells in col D to cells in col C.

[/URL][/IMG]

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H
2			2016/01	42278	3	Apple	42278	number
3	Apple		2016/01	42278	3	Orange	42278	text
4	Orange		2016/01	42278	3
5			2016/01	1-Oct-15	3
6			2016-01	1-Oct-15	3
7			1-Jan-16	1-Oct-15	3
8
9							T10_1703c	2aa
10
2aa

Cell Formulas
Range	Formula
D3	=VLOOKUP(A3,F2:G2,2,0)
D4	=VLOOKUP(A4,F2:G3,2,0)
E2	=DATEDIF(D2,DATEVALUE(C2&"/1"),"m")
E3	=DATEDIF(D3,DATEVALUE(C3&"/1"),"m")
E4	=DATEDIF(D4+0,DATEVALUE(C4&"/1"),"m")
E5	=DATEDIF(D2,DATE(LEFT(C5,4),RIGHT(C5,2),1),"m")
E6	=DATEDIF(D5,C6&"-1","m")
E7	=DATEDIF(MIN(C7:D7),MAX(C7:D7),"m")

 

[Dave Patton]

Minor addition and edit

Excel 2010
	A	B	C	D	E	F	G	H
1								Datedif
2			2016/01	42278	3	Apple	42278	number
3			2016/01	42278	3
4	Apple		2016/01	42278	3	Orange	42278	text
5	Orange		2016/01	42278	3
6			2016/01	1-Oct-15	3
7			2016-01	1-Oct-15	3
8			1-Jan-16	1-Oct-15	3
9			1-Oct-15	1-Jan-16	3
10							T10_1703c	2aa
11
2aa

Cell Formulas
Range	Formula
D4	=VLOOKUP(A4,F2:G2,2,0)
D5	=VLOOKUP(A5,F2:G4,2,0)
E2	=DATEDIF(D2,DATEVALUE(C2&"/1"),"m")
E3	=DATEDIF(D3,(C3&"/1")+0,"m")
E4	=DATEDIF(D4,(C4&"/1"),"m")
E5	=DATEDIF(D5+0,(C5&"/1"),"m")
E6	=DATEDIF(D6,DATE(LEFT(C6,4),RIGHT(C6,2),1),"m")
E7	=DATEDIF(D7,C7&"-1","m")
E8	=DATEDIF(MIN(C8:D8),MAX(C8:D8),"m")
E9	=DATEDIF(C9,D9,"m")

 

[StuartM1]

Dave and Falcondude and snjpverma and Amanda,

Can't thank you enough. So it seems the solution is to get the yyyy/mm to become an actual date, and then compare the two with DateDif. I will save this among my 'reference' type files.

Thank you!!!