Interesting date calculation formula for my student

So I have a issue like this

Jan-15	12
Feb-15	12
Mar-15	12
Apr-15	12
May-15	11
Jun-15	10
Jul-15	9
Aug-15	8
Sep-15	7
Oct-15	6
Nov-15	5
Dec-15	4
Jan-16	3
Feb-16	2
Mar-16	1
Apr-16	0
May-16	0

The left side data will be coming from the formula ,example if an employee has the joining on Jan 15 the formula will give his employment time as 12 month



The reference date for this is 4/1/2016
which is in N2







On G2 I have a date 3/24/2015(I have month/date/year format)


On I2 I should write this

=IF(YEAR(G2)=YEAR($N$2),MONTH($N$2)-MONTH(G2),(YEAR($N$2)-YEAR(G2))*12+(MONTH($N$2)-MONTH(G2)))

and on H2 I have to write this


=IF(I2>=12,12,IF(I2>=11,11,IF(I2>=10,10,IF(I2>=9,9,IF(I2>=8,8,IF(I2>=7,7,IF(I2>=6,6,IF(I2>=5,5,IF(I2>=4,4,IF(I2>=3,3,IF(I2>=2,2,IF(I2>=1,1,IF(I2>=0,0,IF(I2>=-1,0))))))))))))))












voila problem solved







I have added the excel file







but then the student said the formula to wrapped in one column

so

the formula ended up like this


=IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=12,12,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=11,11,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=10,10,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=9,9,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=8,8,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=7,7,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=6,6,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=5,5,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=4,4,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=3,3,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=2,2,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=1,1,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=0,0,IF(IF((IF(YEAR(F2)=YEAR("4/1/2016"),MONTH("4/1/2016")-MONTH(F2),(YEAR("4/1/2016")-YEAR(F2))*12+(MONTH("4/1/2016")-MONTH(F2)))<0>=-1,0,0))))))))))))))





This is the final file


Sourav Bhattacharya
Excel/Excel VBA Teacher 
Mail me at allsourav2atgmaildotcom