Cost Spread Equally between dates

[rafaelgilardino]

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

 

[James006]

Hi,

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

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

Hope this will help

 

[rafaelgilardino]

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:

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

<tbody>
</tbody>

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

Hi,

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

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

Hope this will help

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>

 

[James006]

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

 

[rafaelgilardino]

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

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:

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

<tbody>
</tbody>

 

[James006]

Hello,

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

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 ...