Help with formula

AMC01

New Member
Joined
Jun 3, 2015
Messages
1
Hope someone can help, i'm trying to find an Excel formula which will do the following:-

I have a spreadsheet which has the following data, and i'm trying to enter a formula in column D which will tell me how many items they are allowed to receive in current financial year which runs from 01/04/201* to 31/03/201*.

start date in job (A) end date in job (B) number of items allowed to receive (C) number of items received in current financial year (D)
e.g., 24/7/15 23/7/15 will only have values of 1, 2 or 3 ????? (formula required)

If the number in column C is a 1, then they have to receive the item on the 1st month of their start date, so in the above instance they would have a 1 in column D
If the number in column C is a 2, then they have to receive the 1st item on the 1st month of their start date, and the second item on the 6th month after their start date, so in the above instance they would have a 2 in column D (as both items still in same financial year)
If the number in column C is a 3, then they have to receive the 1st item on the 1st month of their start date, the second item on the 5th month, and the 3rd item on the 9th month, so in the above instance they would still have a 2 in column D (as only 2 of the items would be received in same financial year)

Grateful if anyone can help
Thanks
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Watch MrExcel Video

Forum statistics

Threads
1,126,985
Messages
5,621,985
Members
415,872
Latest member
ReignEternal

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
Top