formula help

[saint_2008]

Hi All, I've been playing about with try to work out a way of finding when the next promotion is due on this sheet, so far all I've seemed to do is make things more complex on the sheet, is there a easy way of find out when someone due there next promotion.

I've included the sheet below...

<table><tr><td>Date</td><td>Grade</td><td>Next Promotion</td></tr><tr><td>03/03/2011</td><td>=VLOOKUP(DATEDIF(A2,TODAY(),"Y"),$A$10:$B$19,2)</td><td></td></tr><tr><td>05/03/1990</td><td>Sergeant</td><td></td></tr><tr><td>14/06/2012</td><td>Lance Corporal</td><td></td></tr><tr><td>10/02/2010</td><td>Corporal</td><td></td></tr><tr><td>01/12/2011</td><td>Lance Corporal</td><td></td></tr><tr><td>11/01/2010</td><td>Corporal</td><td></td></tr><tr><td></td><td></td><td></td></tr><tr><td>Yrs Service</td><td>Rank</td><td></td></tr><tr><td>1</td><td>Private</td><td></td></tr><tr><td>2</td><td>Lance Corporal</td><td></td></tr><tr><td>3</td><td>Lance Corporal</td><td></td></tr><tr><td>4</td><td>Lance Corporal</td><td></td></tr><tr><td>5</td><td>Corporal</td><td></td></tr><tr><td>6</td><td>Corporal</td><td></td></tr><tr><td>7</td><td>Corporal</td><td></td></tr><tr><td>8</td><td>Corporal</td><td></td></tr><tr><td>9</td><td>Corporal</td><td></td></tr><tr><td>10</td><td>Sergeant</td><td></td></tr><tr><td></td></tr></table>

Many Thanks for any help / light shed on how I should be going about this.

 

[Scott Huish]

Is there supposed to be some sort of number of years in the second table to add to the date in the first table?

 

[saint_2008]

Is there supposed to be some sort of number of years in the second table to add to the date in the first table?

The Years are in A10:A19 on the second table I'm just put them as a number as it helps with the lookup in table one. Using the DATEDIF(A2,TODAY(),"Y" Gives me the difference from between the dates.

I'm Struggling on getting Column C to work right, I've tried to do it with a IF statement adding and it just seem to have got more complicated on trying to get the result I was after. I want Column C to show the Date 03/03/2016 as this would be 5th year of service, and this would be when the Next promotion would be from Lance Corporal to Corporal.

I hopefully that explains it a little better and its possible to do.