Split value by months within date range?

sp151515

New Member
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?

CustomerMonthMonthly number bookedMonthly amount booked
AMarch 2013
 207

<colgroup><col><col></colgroup><tbody>
</tbody>
£2,258.06
AApril 2013
 214

<tbody>
</tbody>

 £2,338.71

<tbody>
</tbody>

AMay 201330£322.58

<tbody>
</tbody>

Thanks so much.

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Excel Workbook
ABCDE
1CustomerFromToTotal number bookedTotal amount booked
2A03/03/201305/05/20134585000
3
4CustomerMonthMonthly bookedMonthly amount booked
5AMar-13210.832301.59
6AApr-13218.102380.95
7AMay-1336.35396.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.

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

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

Replies
12
Views
521
Replies
3
Views
198
Replies
3
Views
7K
Replies
5
Views
520
Replies
2
Views
755

1,206,921
Messages
6,075,584
Members
446,147
Latest member
homedecortips

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.

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

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