Calculate value based on date = date in another cell

AMP128

New Member
Joined
Feb 21, 2019
Messages
2
Hi all,
How do I calculate vested shares, in a particular month, based on the vesting date?

For example, I am trying to solve for Cells I2 and N2, the correct answer will be 25,000, however I don't know how to set up the formula to calculate based on the date in row 1.

D2:G2 are the vesting dates.
ABCDEFGHIJKLMNO
1

NameAmountVesting Terms1st2nd3rd4thJan-17Feb-17Mar-17Apr-17May-17Jun-17
2Employee100,00025%Jan-17Jun-17Jun-18Jun-19
3
4

<tbody>
</tbody>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi AMP128,

I made some assumptions since it was not completely clear to me. I am assuming that in Jan-2017 Employee is vested at 25%, in Jun-2017 vested at 50%, Jun-2018 vested at 75%, and Jun-2019 vested at 100%. If that is the case, one possible solution is to use the Match function.

The equation to put in cell I2 would be: =MATCH(J1,$E$2:$H$2,1)*$D$2*$C$2

You can copy that equation to the cells to the right of it.

Hope that helps,

Doug
 
Upvote 0
Sorry my ranges of off by one because I copied your data from above to Excel and had an extra column so the updated formula is: =MATCH(I1,$D$2:$G$2,1)*$C$2*$B$2

Doug
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top