Formula not returning required value in one scenario

[Sean15]

Hi:
Formula in column E is returning required values, except in E9. If start date in A9 falls within date month in D1, formula in E9 should return prorate. Also could formula be shortened as it is becoming long and complicated. Thanks for your help.

Prorate month.xlsx
	A	B	C	D	E	F	G	H
1			Date	5/1/2022
2
3	Start	End						Paid
4	01/01/22	04/28/22			-			1,200.00
5	02/25/22	03/25/22			-			1,100.00
6	02/01/21	03/01/22			-			1,500.00
7	02/15/22	04/15/22			-			1,600.00
8	03/01/22	05/01/22			40.32			1,250.00
9	05/15/22	08/12/22			750.00	required value 350.00		750.00
10	06/01/22	12/07/22			-			1,450.00
11	07/01/22	12/31/22			-			1,600.00
12
Sheet1

Cell Formulas
Range	Formula
E4:E11	E4	=IF(B4<D$1,0,IF(A4>=EOMONTH(D$1,0)+1,0,IF(AND(B4>=D$1,B4<=EOMONTH(D$1,0)),(B4-D$1+1)/DAY(EOMONTH(D$1,0))*H4,H4)))

 

[Fluff]

Why should that be 350, all the If functions return false so it should return H9 which it does.

 

[Sean15]

@Fluff - you are correct. But I want to add an addition step where the formula will check dates in column A, and if the date falls within the date month of D1, prorate amount in column H where necessary. For example, since start date in A9 falls within date month of 05/01/2022 I want it to prorate. Hope this all makes sense

 

[Fluff]

What should the calculation be?

 

[Sean15]

Calculation should be:

(A4-D1+1)/DAY(EOMONTH(D$1,0))*H4

 

[Fluff]

Ok, how about

=IF(B4<D$1,0,IF(A4>=EOMONTH(D$1,0)+1,0,IF(AND(B4>=D$1,B4<=EOMONTH(D$1,0)),(B4-D$1+1)/DAY(EOMONTH(D$1,0))*H4,IF(MONTH(A4)=MONTH($D$1),(A4-D$1+1)/DAY(EOMONTH(D$1,0))*H4))))

but that does not return 350

 

[Sean15]

Yes, my mistake. E9 should return 362.90 not 350.00. I'm currently testing formula. Will let you know outcome.
Thanks for you help.

 

[Fluff]

Ok