I am working with stock options that vest over 5 years. I have to pro-rate
the options based on the date a person went part-time. If the vest date is
prior to the leave date, the answer will be a cell referencing the number of
shares that vest each year (C57). The vest date is prior to the leave date
in Yr 1, 2 & 3 so the answer is C57. The problem is in Yr 4. I need to
pro-rate from 12/12/2007 to 10/06/2008 at full-time and then 10/06/2008 to
12/12/2008 at part-time. Then Yr 5 is C57*.5 (full time equivalency or
'FTE'). I've been using the following formula:
IF(A1<B24,C57,((DATEDIF(A3,B24,"d"/365*C57+DATEDIF(B24,A4,"d"/365*C57*.5). I
need to work in the logic of what happens in Yr 5 when I need it to calculate
C57*.5. I can't use if vest date is greater because it's greater in yr 4 and
5.

Leave Date: 10/06/2008 (B24)
Yr 1: 02/12/2005 (A1)
Yr 2: 12/12/2006 (A2)
Yr 3: 12/12/2007 (A3)
Yr 4: 12/12/2008 (A4)
Yr 5: 02/12/2009 (A5)