Formula to Spread costs out by dates...Why is this so hard??

S

SboothAZ

New Member
Joined
Jul 12, 2005
Messages
38
I am working on a budget and need to spread costs out by a date range:

In cell A2 I have Start Date
In Cell B2 I have End Date
In Cell D2 I have the total cost
In cells O1 thru Z1 I have the months 1/1/2005,2/1/2005.... 12/1/2005
In cells O2 thru Z2 I have the cost per month January thru December.

I am looking for a formula that I can put in each of the cells O2 thru Z2 that will look at the Start Date and End Date and spread the total costs in Cell D1 over that period of time based on the number of days.

For example: The start date for the program is Jan 15th the end date is March 15th. The Total Cost is $40,000. The total number of days between the Start date & end date is 59.
January has 16, February had 28, and March has 15.

$40,000/59 days = 677.97 per day.

so January would have $10,847.52 (677.97 x 16) allocated to it, February would have the largest portion allocated to it (677.97 x 28) etc...

The formulas in the cells O2 thru Z2 would show values in cells O,M, & N (January thru March) and all other cells would be blank.

I have many budgets I need to do this way. So having a formula to automate this is really important.

Any help would be appreciated. If you need more information please let me know.

Steven
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Try this formula in O2, copied across to Z2

=MAX(0,MIN($B2+1,EDATE(O$1,1))-MAX($A2+1,O$1))/($B2-$A2)*$D2

Note: my formula uses the logic of your example but if 15th Jan to 15th March is 59 days, 16 in Jan, 28 in Feb and 15 in March this means you're not including the start date, for instance if your start date is 31st January there would be no cost allocated to January, is that what you require?

If that's not what you want then you probably need to view 15th jan to 15th Mar as 60 days, i.e B2-A2+1 (and I'll need to amend my formula slightly).....to this

=MAX(0,MIN($B2+1,EDATE(O$1,1))-MAX($A2,O$1))/($B2-$A2+1)*$D2
 
Upvote 0
It works!!!!

I can not believe it.... Is there 59 days or 60 days from 1/15/2005 to 3/15/2005?

Steven
 
Upvote 0
Thank You!

I can't express how much this has helped me. I'm still digesting your formula...

Thank you,

Steven
 
Upvote 0
Look at it this way, If A2 was 1st January and B2 31st January then B2-A2 will give you 30.....but of course there are 31 days in January so you need to count both the start date and end date (effectively from the start of 1st January to the end of 31st January) which is equivalent to B2-A2+1, so, for your purposes I would think that you'd want to treat your example as 60 days.
 
Upvote 0
This is kind of sort of what I am trying to figure out at the moment. But to spread whole numbers across the months evenly to give the below figures.

Start and End months are given by month numbers, bigger numbers are skewed toward the end of the year.

1 2 3 4 5 6 7 8 9 10 11 12
Start End Spread Total J F M A M J J A S O N D
Order #1 1 2 Even 10 1 1 1 1 1 1 1 1 1 1
Order #2 3 10 Even 23 2 3 3 3 3 3 3 3
Order #3 3 10 Even 2 2
Order #4 3 12 Even 41 4 4 4 4 4 4 4 4 4 5
Order #5 2 12 Even 33 2 2 2 3 3 3 3 3 3 3 3
Order #6 1 12 Even 63 5 5 5 5 5 5 5 5 5 6 6 6
Order #7 5 8 Even 2 1 1
Order #8 1 12 Even 19 1 1 1 1 1 2 2 2 2 2 2 2
Order #9 1 12 Even 21 1 1 1 2 2 2 2 2 2 2 2 2
Order #10 1 12 Even 29 2 2 2 2 2 2 2 3 3 3 3 3
Order #11 1 12 Even 83 6 7 7 7 7 7 7 7 7 7 7 7
Order #12 12 12 Even 99 99
Order #13 5 12 Even 99 12 12 12 12 12 13 13 13
 
Upvote 0
Hi everyone,
Using excel 2010 and I have a similar question to this thread, but i am looking to distribute costs over weeks instead of months. Weeks start on Monday.
Cell A2 = Start date (8/7/17)
Cell B2 = End date (22/9/17)
Cell C2 = Budget (125,000)
Cells D1 to V1 are dates 26/6/17 though to 30/10/17
I am looking for a formula to distribute the budget to weeks based on days, i.e. first and last weeks would be less than 7 days.
Much appreciated
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
992
felixstraube
F
charlesstricklin
  • Solved
Split week's dates by month?
Replies
7
Views
208
rlv01
R
F
  • Solved
Formula to Spread Cost across every N month
Replies
3
Views
502
Fonzie
F
M
  • Solved
Calculate Date through today
Replies
3
Views
591
Joe4
Joe4
H
  • Question
Payment timeline forecast....
Replies
2
Views
359
hro5e
H

Forum statistics

Threads
1,215,004
Messages
6,122,659
Members
449,091
Latest member
peppernaut

Share this page

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