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.

GregPen's avatar
GregPen
Tin Contributor
Aug 21, 2026

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.