Forum Discussion

Sam Collins's avatar
Sam Collins
Copper Contributor
Oct 10, 2018

Conditional format a cell that corresponds to todays date

Hi all, I have a table of sorts with months running across the top and then days running down the left. The months are the first day of each month but formatted to show the just month in "mmm" forma...
Conditional Formatting
Excel
Formulas and Functions
  • Haytham Amairah's avatar
    Haytham Amairah
    Oct 10, 2018

    Hi Sam,

     

    I've updated the workbook you attached with the below conditional formatting formula to highlight today's date.

    =CELL("address",D4)=ADDRESS(DAY(TODAY())+3,CHOOSE(IF(YEAR(TODAY())=2018,1,2),MONTH(TODAY()),MONTH(TODAY())+12),1)

     

    But, please be careful not to change the current layout of the table because the formula depends on it.

     

    Regards,

    Haytham

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Oct 10, 2018

Hi Sam,

 

I've updated the workbook you attached with the below conditional formatting formula to highlight today's date.

=CELL("address",D4)=ADDRESS(DAY(TODAY())+3,CHOOSE(IF(YEAR(TODAY())=2018,1,2),MONTH(TODAY()),MONTH(TODAY())+12),1)

 

But, please be careful not to change the current layout of the table because the formula depends on it.

 

Regards,

Haytham

  • Sam Collins's avatar
    Sam Collins
    Copper Contributor
    Oct 10, 2018

    Hi Haytham,

     

    Thank you so much - I thought I  had a solution, but it would work when I put it in the Conditional Formatting formula bar. I created this formula that found the correct cell:

    =INDEX($D$4:$AA$34, MATCH(DAY(TODAY()), $B$4:$B$34,0), MATCH(DATEVALUE(MONTH(TODAY())&"/"&(YEAR(TODAY()))), $D$3:$AA$3, 0))

     

    Just out of curiosity, how could this be modified to work in the conditional formatting box? If it can at all!

     

    Again, thank you very much for your help!

     

    Sam

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Oct 10, 2018

      Thank you.

      In fact, I've created a similar formula to this one earlier, but I realized it didn't work!

      This is because the INDEX is a lookup function, and it returns the cell's value, not the cell's address which what we need.

       

      To be able to use this formula, you have to fill in all cells with dates, and then adjust the formula in the conditional formatting rule as follows:

      =D4=INDEX($D$4:$AA$34, MATCH(DAY(TODAY()), $B$4:$B$34,0), MATCH(DATEVALUE(MONTH(TODAY())&"/"&(YEAR(TODAY()))), $D$3:$AA$3, 0))

       

      This is done in the attached workbook.

       

      Hope that helps

      • Sam Collins's avatar
        Sam Collins
        Copper Contributor
        Oct 11, 2018

        Hi Haytham,

         

        Thanks for getting back - it does somewhat in that I now understand why Index won't work in a conditional format. However, unfortunately, your suggestion wouldn't work only because I need the cells to be empty as they will be filled in with events etc.

         

        Nonetheless, thank you so much for your help, I think your original formula will work perfectly!

         

        Sam

Resources