Cost Spread Equally between dates

R

rafaelgilardino

New Member
Joined
Nov 16, 2018
Messages
3
Hi all,

I'm struggling for a quite while trying to create formula that split monthly equally cost based on min & max dates from a range.

Image of my table can be found below:
https://imgur.com/a/6lfa9D7

The idea is spread row values among the min & max dates of red square

Any help will be very much appreciated and will save my job :laugh:
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Hi,

In your cell F4 ... you can test following formula :

Code: 
=ROUND($C4*MAX(0,NETWORKDAYS(MAX($D4,F$2),MIN($E4,EOMONTH(F$2,0))))/NETWORKDAYS($D4,$E4),2)

Hope this will help
 
Upvote 0
Hi all,

I'm struggling for a quite while trying to create formula that split monthly equally cost based on min & max dates from a range.

The idea is spread row values among the min & max dates of red square

In the example below ideally row 11 should be identified as longer task (lasting until column M) and cost of rows from 3 to 10 to be split equally between F until M

Identification can be a problem however just the split would already be a huge help

Table:
[TABLE="class: tableizer-table"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[TH]L[/TH]
[TH]M[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]CR ID[/TD]
[TD]Task Name[/TD]
[TD]Cost[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Aug-19[/TD]
[TD]Sep-19[/TD]
[TD]Oct-19[/TD]
[TD]Nov-19[/TD]
[TD]Dec-19[/TD]
[TD]Jan-20[/TD]
[TD]Feb-20[/TD]
[TD]Mar-20[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AMDOCSFIN-91[/TD]
[TD]Blindagem Final Solution + Double Play/Facilities APPs[/TD]
[TD]$1,204.75[/TD]
[TD]8/21/2019[/TD]
[TD]4/25/2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]OMS[/TD]
[TD]$279.20[/TD]
[TD]8/21/2019[/TD]
[TD]2/16/2020[/TD]
[TD]15.64[/TD]
[TD]46.93[/TD]
[TD]48.50[/TD]
[TD]46.93[/TD]
[TD]48.50[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]ODS[/TD]
[TD]$272.22[/TD]
[TD]8/21/2019[/TD]
[TD]2/11/2020[/TD]
[TD]15.64[/TD]
[TD]46.93[/TD]
[TD]48.50[/TD]
[TD]46.93[/TD]
[TD]48.50[/TD]
[TD]48.50[/TD]
[TD]17.21[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]MEC[/TD]
[TD]$80.27[/TD]
[TD]8/21/2019[/TD]
[TD]10/11/2019[/TD]
[TD]15.74[/TD]
[TD]47.22[/TD]
[TD]17.31[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]MCSS[/TD]
[TD]$101.21[/TD]
[TD]8/21/2019[/TD]
[TD]10/25/2019[/TD]
[TD]15.57[/TD]
[TD]46.71[/TD]
[TD]38.93[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Invoicing[/TD]
[TD]$217.08[/TD]
[TD]8/21/2019[/TD]
[TD]12/29/2019[/TD]
[TD]16.70[/TD]
[TD]50.10[/TD]
[TD]51.77[/TD]
[TD]50.10[/TD]
[TD]48.43[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]AmDD[/TD]
[TD]$52.35[/TD]
[TD]8/21/2019[/TD]
[TD]9/23/2019[/TD]
[TD]15.86[/TD]
[TD]36.49[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]CM[/TD]
[TD]$17.45[/TD]
[TD]8/21/2019[/TD]
[TD]9/10/2019[/TD]
[TD]8.73[/TD]
[TD]8.73[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]WOL[/TD]
[TD]$76.78[/TD]
[TD]8/21/2019[/TD]
[TD]11/17/2019[/TD]
[TD]8.73[/TD]
[TD]26.18[/TD]
[TD]27.05[/TD]
[TD]14.83[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]MCO[/TD]
[TD]$108.19[/TD]
[TD]8/22/2019[/TD]
[TD]4/25/2020[/TD]
[TD]3.94[/TD]
[TD]13.14[/TD]
[TD]13.58[/TD]
[TD]13.14[/TD]
[TD]13.58[/TD]
[TD]13.58[/TD]
[TD]12.70[/TD]
[TD]13.58[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]4236[/TD]
[TD]Alavancagem Ebilling[/TD]
[TD]$604.82[/TD]
[TD]10/25/2019[/TD]
[TD]8/23/2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]OMS[/TD]
[TD]$20.94[/TD]
[TD]10/25/2019[/TD]
[TD]11/7/2019[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]9.66[/TD]
[TD]11.28[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]CRM[/TD]
[TD]$213.94[/TD]
[TD]10/25/2019[/TD]
[TD]3/10/2020[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]9.37[/TD]
[TD]46.85[/TD]
[TD]48.41[/TD]
[TD]48.41[/TD]
[TD]45.29[/TD]
[TD]15.62[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]MCSS[/TD]
[TD]$230.34[/TD]
[TD]10/25/2019[/TD]
[TD]3/20/2020[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]9.40[/TD]
[TD]47.01[/TD]
[TD]48.58[/TD]
[TD]48.58[/TD]
[TD]45.44[/TD]
[TD]31.34[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]AR[/TD]
[TD]$34.90[/TD]
[TD]10/25/2019[/TD]
[TD]11/16/2019[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]9.52[/TD]
[TD]25.38[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]Invoicing[/TD]
[TD]$52.35[/TD]
[TD]12/29/2019[/TD]
[TD]1/31/2020[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]3.17[/TD]
[TD]49.18[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD]MCO[/TD]
[TD]$52.35[/TD]
[TD]4/26/2020[/TD]
[TD]8/23/2020[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

Any help will be very much appreciated and will save my job :laugh:

James006 said:
Hi,

In your cell F4 ... you can test following formula :

Code: 
=ROUND($C4*MAX(0,NETWORKDAYS(MAX($D4,F$2),MIN($E4,EOMONTH(F$2,0))))/NETWORKDAYS($D4,$E4),2)

Hope this will help
Click to expand...

I'm not sure why but I'm not getting any value.

As I could understand from the formula there is no reference for the month of start & ending of each task, how will it be allocated along the months?<style type="text/css"> table.tableizer-table { font-size: 12px; border: 1px solid #CCC ; font-family: Arial, Helvetica, sans-serif; } .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #CCC ; } .tableizer-table th { background-color: #104E8B ; color: #FFF ; font-weight: bold; }</style><style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
 
Upvote 0
Hello,

Please check what you have in your cells F1 to M1 ...

My recommendation for cell F1 : = date(2019,8,1)

And in cell G1 : =EDATE(F1,1)

which can be copied to all your columns ...

Hope this will help
 
Upvote 0
James006 said:
Hello,

Please check what you have in your cells F1 to M1 ...

My recommendation for cell F1 : = date(2019,8,1)

And in cell G1 : =EDATE(F1,1)

which can be copied to all your columns ...

Hope this will help
Click to expand...

I've got some progress however still missing crucial part of splitting it by the min & max dates from the interval. For instance dates from row 11 should be consider as min & max and rows between 3>10 should be equally split between row 11 dates.

Also the split are not equal we have different numbers between the months

result below:
[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[TH]L[/TH]
[TH]M[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]CR ID[/TD]
[TD]Task Name[/TD]
[TD]Cost[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Aug-19[/TD]
[TD]Sep-19[/TD]
[TD]Oct-19[/TD]
[TD]Nov-19[/TD]
[TD]Dec-19[/TD]
[TD]Jan-20[/TD]
[TD]Feb-20[/TD]
[TD]Mar-20[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AMDOCSFIN-91[/TD]
[TD] Blindagem Final Solution + Double Play/Facilities APPs[/TD]
[TD]$1,204.75[/TD]
[TD]8/21/2019[/TD]
[TD]4/25/2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD] OMS[/TD]
[TD]$279.20[/TD]
[TD]8/21/2019[/TD]
[TD]2/16/2020[/TD]
[TD] 17.45[/TD]
[TD] 45.81[/TD]
[TD] 50.17[/TD]
[TD] 45.81[/TD]
[TD] 47.99[/TD]
[TD] 50.17[/TD]
[TD] 21.81[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD] ODS[/TD]
[TD]$272.22[/TD]
[TD]8/21/2019[/TD]
[TD]2/11/2020[/TD]
[TD] 17.42[/TD]
[TD] 45.73[/TD]
[TD] 50.09[/TD]
[TD] 45.73[/TD]
[TD] 47.91[/TD]
[TD] 50.09[/TD]
[TD] 15.24[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD] MEC[/TD]
[TD]$80.27[/TD]
[TD]8/21/2019[/TD]
[TD]10/11/2019[/TD]
[TD] 16.90[/TD]
[TD] 44.36[/TD]
[TD] 19.01[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD] MCSS[/TD]
[TD]$101.21[/TD]
[TD]8/21/2019[/TD]
[TD]10/25/2019[/TD]
[TD] 16.87[/TD]
[TD] 44.28[/TD]
[TD] 40.06[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD] Invoicing[/TD]
[TD]$217.08[/TD]
[TD]8/21/2019[/TD]
[TD]12/29/2019[/TD]
[TD] 18.67[/TD]
[TD] 49.02[/TD]
[TD] 53.69[/TD]
[TD] 49.02[/TD]
[TD] 46.68[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD] AmDD[/TD]
[TD]$52.35[/TD]
[TD]8/21/2019[/TD]
[TD]9/23/2019[/TD]
[TD] 17.45[/TD]
[TD] 34.90[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD] CM[/TD]
[TD]$17.45[/TD]
[TD]8/21/2019[/TD]
[TD]9/10/2019[/TD]
[TD] 9.31[/TD]
[TD] 8.14[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD] WOL[/TD]
[TD]$76.78[/TD]
[TD]8/21/2019[/TD]
[TD]11/17/2019[/TD]
[TD] 9.75[/TD]
[TD] 25.59[/TD]
[TD] 28.03[/TD]
[TD] 13.41[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD] MCO[/TD]
[TD]$108.19[/TD]
[TD]8/22/2019[/TD]
[TD]4/25/2020[/TD]
[TD] 4.28[/TD]
[TD] 12.84[/TD]
[TD] 14.06[/TD]
[TD] 12.84[/TD]
[TD] 13.45[/TD]
[TD] 14.06[/TD]
[TD] 12.22[/TD]
[TD] 13.45[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hello,

If my understanding is correct ... the function seems to return the splits you wanted ... :smile:

Why are the amounts not identical from a month to another ... and your Start and Finish Dates can fall at any point in time in any month ...

Do no forget that the Networkdays function is excluding the Week-Ends ...

Hope this clarifies

Because the number of working days in each and every month is not always the same ...
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Spread Amount between two dates at specific intervals
Replies
3
Views
1K
myall_blues
myall_blues
E
  • Question Question
Spreading Values Across Range of Dates
Replies
4
Views
382
felixstraube
F
B
  • Solved
Amount spread between two dates, use start date unless passed use alternate date
Replies
5
Views
548
myall_blues
myall_blues
E
  • Solved
Minimum value between dates
Replies
6
Views
1K
Tetra201
Tetra201
Taul
  • Solved
Counting working Hours in Specific Month between Two Date/times
2
Replies
10
Views
2K
Taul
Taul

Forum statistics

Threads
1,223,705
Messages
6,173,989
Members
452,541
Latest member
haasro02

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