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" format e.g. 1/4/18 is Apr.

Down the left is just numbers i.e. 1-31 and formatted as such.

What I want to achieve is a conditional format that highlights the corresponding cell to today's date.

I know it must be some sort of Index Match function to find today's month and the year from the top row and then the day from the numbers on the left, but I just cannot seem to do it.

I have attached the workbook for reference.

Thank you very much
Sam

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

Resources