Split Income over month end to recognize a portion in this month and a portion next month?

[Waffles255]

Hi, what formula or approach would you suggest to do the following:

You have a travel tour with the starting date from 26/02/2020 - 15/04/2020 is there a way to split the income for example :

1st split is from 26/02/2020 - 29/02/2020
2nd Split is from 01/03/2020 - 31/02/2020
3rd split is from 01/04/2020 - 15/04/2020

i want to allocate the income per month as the tour progresses the information is for example as follow :

UK tour : from 26/02/2020 - 15/04/2020 Amount $5000

Secondly is there a way to check if the tour will go over month end?

Thanks in advance

 

[Waffles255]

For example i have 2 tours one from 01/02/2020 - 20/02/2020, and one from 06/02/2020 - 05/03/2020 is there a way to see which tours will be conducted over month end? the first tour will not but the second one will

 

[Dave Patton]

T202003a.xlsm
	D	E	F	G	H	I	J	K
2	Start	End	Amount	Total	Jan-20	Feb-20	Mar-20	Apr-20
3	26-Feb-20	15-Apr-20	5,000.00	50	0.00	400.00	3,100.00	1,500.00
4
1d

Cell Formulas
Range	Formula
G3	G3	=E3-D3+1
H3:K3	H3	=MAX(0, MIN(EOMONTH(H$2, 0), $E3) + 1 - MAX(H$2, $D3))*$F$3/$G$3

The month dates are formatted dates such as 1-Jan-2020

 

[Dave Patton]

T202003a.xlsm
	D	E	F	G	H	I	J	K	L	M
2	Start	End	Amount	Total	Jan-20	Feb-20	Mar-20	Apr-20
3	26-Feb-20	15-Apr-20	5,000.00	50	0.00	400.00	3,100.00	1,500.00		3
4	01-Feb-20	20-Feb-20	1,000.00	20	0.00	1,000.00	0.00	0.00		1
5	06-Feb-20	05-Mar-20	1,000.00	29	0.00	827.59	172.41	0.00		2
1d

Cell Formulas
Range	Formula
G3:G5	G3	=E3-D3+1
H3:K5	H3	=MAX(0, MIN(EOMONTH(H$2, 0), $E3) + 1 - MAX(H$2, $D3))*$F3/$G3
M3:M5	M3	=COUNTIF(H3:K3,">0")

 

[Dave Patton]

T202003a.xlsm
	D	E	F	G	H	I	J	K	L	M
1										Months
2	Start	End	Amount	Total	Jan-20	Feb-20	Mar-20	Apr-20
3	26-Feb-20	15-Apr-20	5,000.00	50	0.00	400.00	3,100.00	1,500.00		3
4	01-Feb-20	20-Feb-20	1,000.00	20	0.00	1,000.00	0.00	0.00		1
5	06-Feb-20	05-Mar-20	1,000.00	29	0.00	827.59	172.41	0.00		2
1d

Cell Formulas
Range	Formula
G3:G5	G3	=E3-D3+1
H3:K5	H3	=MAX(0, MIN(EOMONTH(H$2, 0), $E3) + 1 - MAX(H$2, $D3))*$F3/$G3
M3:M5	M3	=COUNTIF(H3:K3,">0")

 

[Waffles255]

greatly appreciated thank you for this

 

[Dave Patton]

Thanks for the feedback.