Forum Discussion

bbsin's avatar
bbsin
Iron Contributor
Jul 29, 2024

conditional formula excel

Hi 

I have a question and need help find 3 items
1) How use conditional formula and highlight the month based on the DOB

2) How to use conditional formula to highlight in the month turn first 60 based on the birth month (eg the last row should highlight Jan - 60)

 

Thank you

Birth dateDec 23Jan 24Feb 24Mar 24Apr 24May 24Jun 24Jul 24Aug 24Sep 24Oct 24Nov 24Dec 24
3/23/194875757576767676767676767676
2/5/195667676868686868686868686868
1/31/196459606060606060606060606060
Formulas and Functions

2 Replies

  • BA_Max's avatar
    BA_Max
    Iron Contributor
    Jul 29, 2024

    bbsin 

     

    1) You can use MONTH within conditional formatting - i.e., 

     

     

    MONTH(A1) = 2​

     

     

    2) This would be easier done if you just used full dates within the top row rather than "Jan 24". You can use formatting to make it appear as Jan 24 after the fact. Doing it this way, you can simply do EDATE and MOD to check if the multiple is of 12 then minus one from the other and round down to see if it's 60.  

     

     

    =( (MOD(EDATE(A1,B1),12) = 0) * ((B1 - A1) > 60) )​

     

     

    I believe something like that should work but I've not tested it - hopefully the idea carries in the formula.

     

Resources