Allocation Across Months

[ChetanPuri]

Good Afternoon Excel Expert team,

Need some help regarding Revenue Allocation across Month Formula, I am trying to create formula which Calculate the amount in K3, first based on Number of days between Column I3 Start Date and Column J3 End Date, then in Column R3, It should refer to EOM Dates on Column R2 to AC2 and allocate Revenue based on number of days in that month for example 158.71* 31 Days or 158.71*28 etc. There is one more criteria that I need to consider is in Column R 1 and S1, I have a Start date of the Financial Year and in S1 I have the month end Date. I need a formula where the Daily amount is based on Start and End Date of the contract but should only calculate up to EOM column as per date in S1. Thank you for all your help in advance

AR SDA Review -24 May 2024.xlsx
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1										1/07/2023	30/04/2024
2	START_DATE	END_DATE	ALLOCATED_AMOUNT	REMAINING_AMOUNT	ACCRUAL_AMOUNT	LAST_MODIF_DATE	STATUS	No. of Days	Daily Amount	31-Jul-23	31-Aug-23	30-Sep-23	31-Oct-23	30-Nov-23	31-Dec-23	31-Jan-24	29-Feb-24	31-Mar-24	30-Apr-24	31-May-24	30-Jun-24
3	1/07/2023	11/12/2023	$26,028.00	$18,134.26		29/02/2024	Active	164	158.71	4,919.93	4,919.93	4,919.93	4,919.93	4,919.93	4,919.93	4,919.93	4,919.93	4,919.93	4,919.93	4,919.93	4,919.93
4	12/12/2023	11/12/2024	$26,028.00	$18,134.26		29/02/2024	Active	366	71.11	0.00	0.00	0.00	0.00	0.00	1,422.30	2,204.56	2,062.33	2,204.56	2,133.44	0.00	0.00
5	16/05/2023	15/05/2024	$9,572.30	$1,193.50		14/04/2024	Active	366	26.15	810.77	810.77	784.61	810.77	784.61	810.77	810.77	758.46	810.77	784.61	0.00	0.00
6	10/11/2023	9/11/2024	$12,703.86	$8,076.29		14/04/2024	Active	366	34.71	0.00	0.00	0.00	0.00	728.91	1,076.01	1,076.01	1,006.59	1,076.01	1,041.30	0.00	0.00
7	17/11/2022	16/11/2024	$17,282.04	$5,995.69		14/04/2024	Active	731	23.64	732.89	732.89	709.25	732.89	709.25	732.89	732.89	685.61	732.89	709.25	0.00	0.00
8	6/03/2023	5/03/2025	$18,632.73	$9,303.62		5/03/2024	Active	731	25.49	790.17	790.17	764.68	790.17	764.68	790.17	790.17	739.19	790.17	764.68	0.00	0.00
9	30/01/2024	29/01/2025	$68,273.64	$56,731.80		14/04/2024	Active	366	186.54	0.00	0.00	0.00	0.00	0.00	0.00	373.08	5,409.66	5,782.74	5,596.20	0.00	0.00
10	10/02/2023	29/01/2024	$60,609.72	$9,036.95		30/01/2024	Inactive	354	171.21	5,307.63	5,307.63	5,136.42	5,307.63	5,136.42	5,307.63	5,307.63	4,965.20	5,307.63	5,136.42	0.00	0.00
11	8/09/2023	7/09/2024	$24,492.85	$24,492.85		8/09/2023	Active	366	66.92	0.00	0.00	1,539.17	2,074.53	2,007.61	2,074.53	2,074.53	1,940.69	2,074.53	2,007.61	0.00	0.00
12	19/09/2023	18/09/2024	$0.00	$0.00		30/11/2023	Active	366	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
13	10/01/2024	9/01/2025	$12,703.92	$9,862.20		14/04/2024	Active	366	34.71	0.00	0.00	0.00	0.00	0.00	0.00	763.62	1,006.59	1,076.02	1,041.30	0.00	0.00
14	23/03/2022	22/03/2025	$34,481.00	$12,393.05		22/03/2024	Active	1096	31.46	975.28	975.28	943.82	975.28	943.82	975.28	975.28	912.36	975.28	943.82	0.00	0.00
NDIS-SCS 29042024

Cell Formulas
Range	Formula
S1	S1	='3 Way Rec - 2'!E1
R2:AC2	R2	='3 Way Rec - 2'!C3
P3:P14	P3	=J3-I3+1
Q3:Q14	Q3	=K3/P3
R3:AC3	R3	=IFERROR(($K3/($J3-$I3+1))*(EOMONTH(MIN(MAX($I3,$R$1),$S$1),0)-MAX($I3,$R$1)+1),"")
R4:AC14	R4	=IFERROR($Q4*((MAX(R$2-$I4,0)-MAX(EOMONTH(R$2,-1)-$I4,0))-(MAX(R$2-$S$1,0)-MAX(EOMONTH(R$2,-1)-$S$1,0))+(EOMONTH(R$2,0)=EOMONTH($I4,0))),"")

 

[NickFru]

Hello - try these formulas, I have inserted some additional columns and some slight variations to your formulas;

I have inserted in column K next to the end date (labelled it) No Of Days In Month - =IF(DAY(EOMONTH(I3,0))>0,DAY(EOMONTH(I3,0). This will give number of days, in this instance, 31 being for July
I have inserted in column Q next to the status heading, No Of Days (As You Have Above) - =IF(I3<EOMONTH($S$1,0),IF(J3<EOMONTH($S$1,0),DATEDIF(I3,J3,"D")+1),""). This will give number of days in month between your current dates in I3 & J3.
Under daily amount, I have used, =IFERROR(ROUND(IF(DAY(EOMONTH(I3,0)<EOMONTH($S$1,0)),L3/Q3),2),"")
Then from S3 to AD3 to give same monthly amount, = IFERROR(IF(ISTEXT(Q3&R3),IF(DATE(YEAR(S2),MONTH(S2),DAY(S2)<EOMONTH($I$3,0)),MAX(INT($K$3)),DATE(YEAR(S2),MONTH(S2),DAY(S2)<EOMONTH($I$3,0)))*$R$3),"")))

If you were to change the date in cell I3 being the start date after 30/04/2024 and in J3, exactly the same, the number of days, daily amount and each monthly amount will be blank or zero.

Hope this may help.

 

[ChetanPuri]

Hello - try these formulas, I have inserted some additional columns and some slight variations to your formulas;

I have inserted in column K next to the end date (labelled it) No Of Days In Month - =IF(DAY(EOMONTH(I3,0))>0,DAY(EOMONTH(I3,0). This will give number of days, in this instance, 31 being for July
I have inserted in column Q next to the status heading, No Of Days (As You Have Above) - =IF(I3<EOMONTH($S$1,0),IF(J3<EOMONTH($S$1,0),DATEDIF(I3,J3,"D")+1),""). This will give number of days in month between your current dates in I3 & J3.
Under daily amount, I have used, =IFERROR(ROUND(IF(DAY(EOMONTH(I3,0)<EOMONTH($S$1,0)),L3/Q3),2),"")
Then from S3 to AD3 to give same monthly amount, = IFERROR(IF(ISTEXT(Q3&R3),IF(DATE(YEAR(S2),MONTH(S2),DAY(S2)<EOMONTH($I$3,0)),MAX(INT($K$3)),DATE(YEAR(S2),MONTH(S2),DAY(S2)<EOMONTH($I$3,0)))*$R$3),"")))

If you were to change the date in cell I3 being the start date after 30/04/2024 and in J3, exactly the same, the number of days, daily amount and each monthly amount will be blank or zero.

Hope this may help.

Hi Nick,
Thank you for all your help, and I apologise for the late response. I just saw this message

 

[NickFru]

Hi Chetan - ah no problem at all, yes, I thought that I'd have more columns inserted to add to your original data so that you can see the difference in days as well as recognising what month is relative to the calculations prior to 30/04/2024.

Take care and best wishes.