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
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
