Forum Discussion

GregPen's avatar
GregPen
Tin Contributor
Aug 20, 2026
Solved

Nested IF function with a checkbox

Hello Excel Gurus, I have a nested IF function that is based on comparing dates and whether a checkbox is selected. It's an 18 month outlook and if the checkbox is selected it should display "O/H" o...
excel
  • Olufemi7's avatar
    Olufemi7
    Aug 21, 2026

    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.

m_tarler's avatar
m_tarler
Silver Contributor
Aug 20, 2026

The formula is:

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

But if $M36 is NOT "" then it will not do the IF($AF36...) because that is part of the TRUE and it will do the IF(N$6...) statement instead.  I'm not sure what to reccommend because I'm not clear on what conditionals you are checking and corresponding outputs you are expecting in each case.

You might want to consider IFS(...) or other alternatives instead of the nested IF structure.

GregPen's avatar
GregPen
Tin Contributor
Aug 20, 2026

I just don't know why it works just fine if there are 1s across all 18 months, but not when there are less.