calculating months based on specified number of days

Hello,

I am trying to figure out a way to calculate months in columns without exceeding a specified number of days. Example:

Person A starts - 8/15/16 - Allocate partial month; full month; full month; allocate partial month
Person B starts - 8/1/16 - allocate full month; allocate full month; allocate full month
Person C starts - 9/21/16 - etc.

The specified number of days is 90. I need to allocate say 100,000 for each of them over the 90 days where it prorates the beginning month and ending month if it isn't a full month but spreads the full months evenly and not using number of days in the month where the full month of January would not equal the full month of February due to less days in February. It would need to stop calculating at 90. However, one person may get set to 120 days and another at 60 days. I am trying not to use VBA in this case to make it a simple workbook. It is for another person to use and didn't want to get into the VBA maintenance with them.

Are you able to post a sample workbook (Use: Go Advanced --> Manage Attachments) so we can see what you're working with? Please be sure to remove or alter any sensitive data.

see attached. Currently they are using goal seek to get to the amounts. We have tried using formulas with Days360 and other methods but we do not get to the result goal seek gets to.

See this ...

Is 120 the maximum number of days? 120 days could span into 5 months, which your current setup isn't really equipped for

I don't know if I understood the task the same way Phuocam did, but take a look at the attachment to see if I got there...

I built it out to span five months, so it should be good for any number of days up to and including 120 days. I made tweaks to columns H, J, and K. I didn't notice column I, so you could actually use I to make the formula in J a bit shorter. Regardless, to get the base amount per month, I used:

=$F$5/($H$7/DAY(EOMONTH($G$7,0))+$H$8/DAY(EOMONTH($G$8,0))+$H$9/DAY(EOMONTH($G$9,0))+$H$10/DAY(EOMONTH($G$10,0))+$H$11/DAY(EOMONTH($G$11,0)))

For the amount in a particular month, I used a slightly more flexible version of your formula:
=H7/DAY(EOMONTH(G7,0))*J7

I made some slight adjustments to column H to account for a fifth month and make the returns incorporate the possibility of 2,3, or 5 months. The end results match your results in columns A:D, which I assumed was the goal. Hopefully I was right? Or at least helpfully wrong?

Looks great and ties out nicely. Thanks for all the help.

My pleasure, good luck!