Split value by months within date range?

[sp151515]

Hi there,

I'm trying to split an amount within a given month:

Customer	From	To	Total number booked	Total amount booked
A	3 March 2013	5 May 2013	458	£5,000

<tbody>
</tbody>

Therefore, assuming that the number and amount booked is done evenly on a daily basis and considering the fact that in March, the period is 3 March - 31 March, while in May it is 1 May - 5 May, is it possible to have a formula that calculates the amount by month?

I have been using =D2/(DAYS360(B2,C2)) to find out the daily number booked, then multiplying this by the days in a given month to find out the monthly value, but is there a more elegant way of doing this where I can define the month in another sheet (Col. B), to return the following in Col. C and D?

Customer	Month	Monthly number booked	Monthly amount booked
A	March 2013	207 <colgroup><col><col></colgroup><tbody> </tbody>	£2,258.06
A	April 2013	214 <tbody> </tbody>	£2,338.71 <tbody> </tbody>
A	May 2013	30	£322.58

<tbody>
</tbody>

Any help you can give me would be much appreciated.

Thanks so much.

 

[roentgen]

Excel Workbook
	A	B	C	D	E
1	Customer	From	To	Total number booked	Total amount booked
2	A	03/03/2013	05/05/2013	458	5000
3
4	Customer	Month	Monthly booked	Monthly amount booked
5	A	Mar-13	210.83	2301.59
6	A	Apr-13	218.10	2380.95
7	A	May-13	36.35	396.83
Month Distribute

These use DATEDIF to calculate the total number of days exactly where as your example uses DAYS360. You could also use NETWORKDAYS in the formula to calculate workdays only. Please check out the C Pearson link Distributing Days Over Intervals
where the main bulk of this formula originates from and explanation on how it works can be found.

 

[barry houdini]

These use DATEDIF to calculate the total number of days exactly....

You can also just subtract, e.g. =C$2-B$2