Project Bonuses

[Cortlyn]

Hi,
I have a table with 5 bonus amounts, 1 for each year running from, 01/Sept/07 to 31/Aug/12 for example:
01/09/07 to 31/08/08 = £1000
01/09/08 to 31/08/09 = £2000
01/09/09 to 31/08/10 = £3000
01/09/10 to 31/08/11 = £4000
01/09/11 to 31/08/12 = £5000
I am trying to write a formula in a spreadsheet/form that will look at a date, that could be anytime from the start date or thereafter that is entered and then calculate bonus due from that date to the end of the project at 31/08/12.

That means if the project join date is 01/09/07 the amount due will be £1K+£2K+£3K+£4K+£5K = £15K etc.. If the start date is part way through a year it will pro rata the part year and add the remaining full years to 31/08/12.

Hope this makes sense.

Thank you.

[daddylonglegs]

Here's one way...see attached

Note the formula in B10 copied down needs to be confirmed with CTRL+SHIFT+ENTER

[Cortlyn]

Hi
Thanks that works. What I did not think of at the time is, what to do should I need to calculate a bonus up to a date before the project end date i.e. an employee leaves early.

Thanks

Cortlyn