Nested IF function with a checkbox

You can make the formula easier to follow by calculating the date once and then deciding whether that month is active. If the checkbox is linked to AF36 and returns TRUE/FALSE, try this Microsoft 365 formula:

=LET(pdd,IFERROR(--$M36,0),active,AND($H36<>"",OR(pdd=0,N$6<pdd)),IF(active,IF($AF36,"O/H",1),""))

Enter it in the first month cell and fill it across the 18-month range. The logic is: H36 must contain a value; a blank or invalid PDD means the month remains active; a real PDD blanks months on or after that date; and the checkbox changes only an active result from 1 to O/H. This avoids calling EDATE on a blank, a space, or text, which is what produces the #VALUE! result. Also confirm that AF36 contains the Boolean value TRUE or FALSE rather than the text “TRUE” or “FALSE.” Using LET here makes each condition visible and prevents the checkbox branch from being skipped inside a nested IF.

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))

I don't know why my responses won't post as replies to your posts, but here's what I found:

NathanReed​ Your formula kind of works as it changes the 1s to O/H as long as there is a date in the PDD block. If there is no date then all the cells get a VALUE error. I think the AND function is needed at the end to check that if there is a "1" AND the box is checked, then put "O/H".

Olufemi7​ Your formula works better, but it changes all the cells to O/H in the row instead of just the cells with a 1. I think an AND function needs to be added as a final check in the formula but when I tried to come up with one I get a "too many arguments" error.

It works when all 18 months contain 1 because, in that case, M36 is blank and the formula reaches IF($AF36,"O/H",1).

When fewer months contain 1, M36 contains a date, so Excel uses this branch instead:

IF(N$6>=EDATE($M36,0),"",1)

That branch never checks AF36, which is why the remaining 1s do not change to O/H.

Use this formula instead:

=IF($H36="","",IF(AND($M36<>"",N$6>=EDATE($M36,0)),"",IF($AF36,"O/H",1)))

1. Enter it in the first month cell.
2. Copy it across all 18 months.
3. Select the checkbox linked to AF36.

Months beyond the date cutoff will remain blank. Any month that would normally display 1 will change to O/H when the checkbox is selected.

Hello GregPen​, 

The key is to decide which condition has priority.

If checking AF36 should always return O/H, regardless of whether M36 contains a date, then the checkbox test needs to be evaluated before the M36 test:

=IF($H36="","",IF($AF36,"O/H",IF($M36="",1,IF(N$6>=EDATE($M36,0),"",1))))

With this order, Excel evaluates:

H36 blank → blank

AF36 checked → O/H

M36 blank → 1

Otherwise → apply the date comparison

The original formula only checks AF36 when M36 is blank. Once M36 contains a date, Excel follows the other branch and AF36 is never evaluated.

If O/H is supposed to depend on both the checkbox and the date condition, then the checkbox needs to be incorporated into that date branch instead.

Here is what it looks like. I cut it down from 18 months to fit in a smaller picture but still show the problem. 

Why didn't it change the two 1s to O/H.