Base on my sheet the M7 cell which the joined date have got a formula like this =VLOOKUP($C$3,VALIDATION!$A$2:$M$500,9,FALSE), so when I choose in C3 (drop-down list, different employee number will show) & the joined date in cell M7 will change, so from there the number of months in J11 will change according to the joined date. IF M7 GOT 1/3/2011 joined date (IT NEED TO BE CHANGE AS 1/3/2013 [2013 'coz preivous year of this year) SUBTRACT TO TODAY DATE 25/10/2014 SO I NEED THE ANSWER OF 19MONTHS (because 1/3/2013 to 1/3/2014 = 12 months or 1 year then + from 1/3/2014 to today date 26/10/2014 = 7 months so MUST BE TOTAL OF 12+7 = 19MONTHS... then only I can count how many entitled leave they can have to date. Somebody give me the formula it work only if the joined date is the same as this year 2014. but if need to count also from 2013 to 2014 (if employee is an old worker) then it has an error and it shows everytime is 15. I really need this badly. thank you.. Here's the formula..
=IF(YEAR(TODAY())>YEAR(M7),((YEAR(TODAY())-YEAR(DATE(YEAR(TODAY())-1,MONTH(M7),1)))*12)+(MONTH(TODAY())-MONTH(YEAR(DATE(YEAR(TODAY())-1,MONTH(M7),1)))),MONTH(TODAY())-MONTH(M7))
=IF(YEAR(TODAY())>YEAR(M7),((YEAR(TODAY())-YEAR(DATE(YEAR(TODAY())-1,MONTH(M7),1)))*12)+(MONTH(TODAY())-MONTH(YEAR(DATE(YEAR(TODAY())-1,MONTH(M7),1)))),MONTH(TODAY())-MONTH(M7))