No correct calculation with Nested IF + AND Function

[LearningByDoing]

Hello,
I have the data ranges shown in the screenshot (columns A-E) and I am trying to use the IF function to fill in the time series (columns G-N) from them. It actually works except for the fact that the first month is not calculated in each case.

To understand better:
B9 has the value 1,000,000 - E9 has the value 3 - C9 is the start month and D9 is the end date.
The IF function should distribute one third of the start month to the end date each month.
Where is my mistake in the formula that the first month is not calculated?
Thank you in advance for your support.

cost_baseline_expenditure_schedule_-_template_EUR.xlsx
	A	B	C	D	E	F	G	H	I	J
6	DURATA IN MESE	7		TOTALI MENSILI				€333,333.33	€608,333.33	€225,445.00
7	PROGETTO COSTI TOTALE	2,065,890.00 €
8	Categoria	COSTI TOTALE	INIZIO	FINE	DURATA		gen-2024	feb-2024	mar-2024	apr-2024
9	CAT_01	€1,000,000.00	Jan-24	Mar-24	3			€333,333.33	€333,333.33
10	CAT_02	€250,000.00	Feb-24	Mar-24	2				€125,000.00
11	CAT_03	€150,000.00	Feb-24	Apr-24	2				€75,000.00	€75,000.00
12	CAT_04	€75,000.00	Mar-24	Apr-24	2					€37,500.00
AUTO_PROSPETTO_COSTI

Cell Formulas
Range	Formula
G6:J6	G6	=IF(SUM(G9:G200)=0,"",SUM(G9:G200))
B6	B6	=SETUP_BASELINE!F5
B7	B7	=COSTESTIMATE
G8	G8	=STARTDATE
H8:J8	H8	=IF(G8<ENDDATE,EDATE(G8,1),"")
G9:J12	G9	=IF(G$8="","",IF(AND(G$8>=$C9,G$8<=$D9),($B9/$E9),""))
A9:E12	A9	=IF(SETUP_BASELINE!B9="","",SETUP_BASELINE!B9:F9)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
COSTESTIMATE	=SETUP_BASELINE!$C$5	B7
ENDDATE	=AUTO_PROSPETTO_COSTI!$B$5	H8:J8
STARTDATE	=AUTO_PROSPETTO_COSTI!$B$4	G8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E9:E201	Cell	does not contain a blank value	text	NO
G6:BB6,H7:BB7	Cell	does not contain a blank value	text	NO
G8:H8,K8:BB8	Cell	does not contain a blank value	text	NO
I8:J8	Cell	does not contain a blank value	text	NO

 

[LearningByDoing]

... with a little trial and error, I found out what the problem was. The dates in columns C and D are taken from another sheet using the IF function in cell A9. If the date is not entered exactly on the first of the month, the problem described above occurs. Why this is the case is beyond my knowledge. Does anyone have any ideas?

 

[Alex Blakenburg]

If you are not going to allocate down to partial months, give this a try:

=IF(G$8="","",IF(AND(G$8>=EOMONTH($C9,-1)+1,G$8<=$D9),($B9/$E9),""))

 

[LearningByDoing]

... @Alex Blakenburg
Thank you very much, that's the solution.
I had toyed with EOMONTH too, but wanted to take a closer look at the other date functions first. Now that's been taken care of.

 

[Alex Blakenburg]

You're welcome. Glad I could help.