add costs every nth month

[deckerp]

Hi, I have a table where I would like to add amounts every x dates.

every service has a different start and specific end date:

Service 1 > 300 € > 4 x per year > Start 15th June 2020 > unlimited (thus 31.12.2099) ..... should be Jun, Sep, Dec, Mar
Service 2 > 250 € > 2 x per year > Start 15th June 2020 > End 14th June 2020 .... should be Jun, Dec
Service 3 > 250 € > 1 x per year > Start 1st September 2020 > End 30th August 2025 .... every 1st of Sep
Service 4 > 1500 € > 6 x per year > Start 1st January 2023 > no End date .... should be Jan, Mar, May, Jul, Sep, Nov
Service 5 > 120 € > 12 x per year > Start 1st of August 2022 > End 30th of August 2025 .... should be every month

the calendar is monthly or yearly
How can I know that the fee should be applied in a specific month (eg Nov 2020)?
How can I find out how the total costs as per service fee in a calendar year?

Many thanks for your help.

 

[deckerp]

Hello, I would really appreciate some help on this. (still no solution found)

 

[Alex Blakenburg]

See if this layout works for you, it might encourage some others to respond to you as well.
I assume you don't want to charge a fee in month 1 (start date month)

20220804 Phase Amount deckerp.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1	Service	Start	End	Frequency	Mths	Amt	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23
2	1	15/06/2020		4	Jun,Sep,Dec,Mar	300	0	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300
3	2	15/06/2020	14/06/2020	2	Jun,Dec	250	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	3	1/09/2020	30/08/2025	1	Sep	250	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	250	0	0	0	0	0	0	0	0	0	0	0	250	0	0	0	0	0	0
5	4	1/01/2023		6	Jan,Mar,May,Jul,Sep,Nov	1500	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1500	1500
6	5	1/08/2022	30/08/2025	12		120	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	120	120	120	120	120	120	120
Sheet1

Cell Formulas
Range	Formula
G2	G2	=IF( OR(G$1<$B2,AND($C2<>"",G$1>$C2),EOMONTH($B2,0)=EOMONTH(G$1,0)), 0, IF(MOD(DATEDIF(EOMONTH($B2,0),EOMONTH(G$1,0),"m"),(12/$D2))=0, $F2, 0))
H2:AN2,G3:AN6	H2	=IF(OR(H$1<$B2,AND($C2<>"",H$1>$C2),EOMONTH($B2,0)=EOMONTH(H$1,0)),0,IF(MOD(DATEDIF(EOMONTH($B2,0),EOMONTH(H$1,0),"m"),(12/$D2))=0,$F2,0))

 

[deckerp]

See if this layout works for you, it might encourage some others to respond to you as well.
I assume you don't want to charge a fee in month 1 (start date month)

20220804 Phase Amount deckerp.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1	Service	Start	End	Frequency	Mths	Amt	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23
2	1	15/06/2020		4	Jun,Sep,Dec,Mar	300	0	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300	0	0	300
3	2	15/06/2020	14/06/2020	2	Jun,Dec	250	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	3	1/09/2020	30/08/2025	1	Sep	250	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	250	0	0	0	0	0	0	0	0	0	0	0	250	0	0	0	0	0	0
5	4	1/01/2023		6	Jan,Mar,May,Jul,Sep,Nov	1500	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1500	1500
6	5	1/08/2022	30/08/2025	12		120	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	120	120	120	120	120	120	120
Sheet1

Cell Formulas
Range	Formula
G2	G2	=IF( OR(G$1<$B2,AND($C2<>"",G$1>$C2),EOMONTH($B2,0)=EOMONTH(G$1,0)), 0, IF(MOD(DATEDIF(EOMONTH($B2,0),EOMONTH(G$1,0),"m"),(12/$D2))=0, $F2, 0))
H2:AN2,G3:AN6	H2	=IF(OR(H$1<$B2,AND($C2<>"",H$1>$C2),EOMONTH($B2,0)=EOMONTH(H$1,0)),0,IF(MOD(DATEDIF(EOMONTH($B2,0),EOMONTH(H$1,0),"m"),(12/$D2))=0,$F2,0))

Many thanks; How would the formular need to be adjusted to respect the beginning month, too?

 

[deckerp]

the month 1 should also be invoiced fully (start date month)

 

[deckerp]

=IF(
OR(EOMONTH(G$1;0)<$B2;AND($C2<>"";G$1>$C2));
0;
IF(MOD(DATEDIF(EOMONTH($B2;0);EOMONTH(G$1;0);"m");(12/$D2))=0;
$F2;
0))
this works for me
again - much appreciated. Have a great day

 

[deckerp]

I wrote to early - it does not work. Please look again into the formula. Many thanks

 

[Alex Blakenburg]

Can you give some examples of what it is doing and what you need it to do.

 

[Sanjeev1976]

Try this :

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV
1	Service	Start	End	Frequency	Amount	Jun'20	Jul'20	Aug'20	Sep'20	Oct'20	Nov'20	Dec'20	Jan'21	Feb'21	Mar'21	Apr'21	May'21	Jun'21	Jul'21	Aug'21	Sep'21	Oct'21	Nov'21	Dec'21	Jan'22	Feb'22	Mar'22	Apr'22	May'22	Jun'22	Jul'22	Aug'22	Sep'22	Oct'22	Nov'22	Dec'22	Jan'23	Feb'23	Mar'23	Apr'23	May'23	Jun'23	Jul'23	Aug'23	Sep'23	Oct'23	Nov'23	Dec'23
2	1	15/06/2020		4	300	300	-	-	300	-	-	300	-	-	300	-	-	300	-	-	300	-	-	300	-	-	300	-	-	300	-	-	300	-	-	300	-	-	300	-	-	300	-	-	300	-	-	300
3	2	15/06/2020	14/06/2022	2	250	250	-	-	-	-	-	250	-	-	-	-	-	250	-	-	-	-	-	250	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
4	3	01/09/2020	30/08/2025	1	250	-	-	-	250	-	-	-	-	-	-	-	-	-	-	-	250	-	-	-	-	-	-	-	-	-	-	-	250	-	-	-	-	-	-	-	-	-	-	-	250	-	-	-
5	4	01/01/2023		6	1,500	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1,500	-	1,500	-	1,500	-	1,500	-	1,500	-	1,500	-
6	5	01/08/2022	30/08/2025	12	120	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	120	120	120	120	120	120	120	120	120	120	120	120	120	120	120	120	120
Sheet1

Cell Formulas
Range	Formula
G1:AV1	G1	=EOMONTH(F1,1)
F2:AV6	F2	=IF(AND(F$1>=$B2,OR(F$1<=$C2,$C2="")),(MOD(DATEDIF($B2,F$1,"m")+(12/$D2),(12/$D2))=0)*$E2,0)
A3:A6	A3	=A2+1