Combining "AND" and "OR" and "IF" formulas from multiple cells

[thbutterflycollector_]

Hi,

I am trying to devise a formula which pays an amount of money based on whether a milestone date is met. This amount is then gradually reduced on whether the date is within a certain period of time eventually being zero. The Formulas are in Row K6 to N6. Thank you

Pain Gain Calculation.xlsx
	F	G	H	I	J	K	L	M	N
5	Contractor	Share of Programme Incentive Payment	Actual Date Milestone Achieved	6 Weeks	6 to 12 Weeks
6	Contractor	£7,000	02-Dec-23	12-Jan-24	23-Feb-24	FALSE	6,300.00	FALSE	FALSE
1 Major Programme Milestones

Cell Formulas
Range	Formula
I6	I6	=E6+42
J6	J6	=E6+84
K6	K6	=IF(H6<=E6,G6)
L6	L6	=IF(AND(H6>E6,H6<=I6),G6*90%)
M6	M6	=IF(AND(H6>I6,H6<=J6),G6*50%)
N6	N6	=IF(H6>J6,0)

Named Ranges
Name	Refers To	Cells
Actually_Achieved	='1 Major Programme Milestones'!$H$6:$H$89	K6:N6
Alliance_Milestone	='1 Major Programme Milestones'!$E$6:$E$89	I6:L6

 

[Sufiyan97]

Try

=IF(H6<=E6,G6,IF(AND(H6>E6,H6<=I6),G6*90%,IF(AND(H6>I6,H6<=J6),G6*50%,IF(H6>J6,0,""))))

 

[thbutterflycollector_]

Thank you! This works - however if the milestone date has not yet been met as it is a date in the future I'd like that formula to take account of the blank cell and not calculate, I've tried
=IF(ISBLANK(H6),"",(H6<=E6,G6,IF(AND(H6>E6,H6<=I6),G6*90%,IF(AND(H6>I6,H6<=J6),G6*50%,IF(H6>J6,0,""))))) but when there is a date inserted in H6 I get a #VALUE! message.

 

[Sufiyan97]

you missed

=IF(ISBLANK(H6),"",IF(H6<=E6,G6,IF(AND(H6>E6,H6<=I6),G6*90%,IF(AND(H6>I6,H6<=J6),G6*50%,IF(H6>J6,0,"")))))


Try

=IF(H6="","",IF(H6<=E6,G6,IF(AND(H6>E6,H6<=I6),G6*90%,IF(AND(H6>I6,H6<=J6),G6*50%,IF(H6>J6,0,"")))))

 

[thbutterflycollector_]

Thank you.