Forum Discussion
Nested IF function with a checkbox
Hello GregPen,
That clarification makes the intended logic clearer.
The checkbox should only change a cell that would otherwise contain 1. It should not override cells that the date logic would leave blank.
Try:
=IF($H36="","",IF($M36="",IF($AF36,"O/H",1),IF(N$6>=EDATE($M36,0),"",IF($AF36,"O/H",1))))
The key is that AF36 is checked only after the formula determines that the result for that month should be 1.
The logic is:
H36 is blank → blank
M36 is blank → 1, or O/H if AF36 is TRUE
N6 is on or after the PDD date → blank
Otherwise → 1, or O/H if AF36 is TRUE
This keeps the existing date cutoff while replacing only the 1s with O/H when the checkbox is selected.
It also keeps the AF36 test out of the EDATE calculation when M36 is blank, avoiding the #VALUE! problem.
Maybe we can group the conditions together better:
=IF(OR($H36="",N$6<$M36),"",IF($AF36,"O/H",1))I think this will work but not sure if I have all your conditions correct but based on Olufemi7 logic this should work becauce if M36 is blank that is treated as 0 and any date is > 0 so it will do the O/H or 1 check.
BTW I removed the EDATE($M36,0) because as far I can tell it doesn't do anything because a change of 0 months is still the same date, but if there is some other reason you have it there then feel free to put it back in.
EDIT: I notice you mentioned you were getting #VALUE! error which I suspect is due to the M36 NOT being blank ("") but a space (" ") or some other text instead, which will mess with a bunch of these formulas so in the formula below I added the edate back in with an IFERROR to catch that problem.
=IF(OR($H36="",N$6<IFERROR(EDATE($M36,0),0)),"",IF($AF36,"O/H",1))