Forum Discussion

williamgurney's avatar
williamgurney
Copper Contributor
May 22, 2024

Using CHAR(COLUMN()+64) within Conditional Formatting

Hi,

 

I have a need to highlight a set of cells based on whether or not a date (date is concatenated based on multiple other cells) =TODAY().

 

Heres the background:

A1 = "2024"

A2 through A61 = "January" through "December"

I2 though AS2 = "1", "2", "3" through "31" (Notice from screenshot that the columns are driven by day so not every month starts on the same day... i.e. Jan 1st is a Monday, but Feb 1st is a Thursday. So the columns are offset).

 

 

=DATE($A$1,1,I2) will return "1/1/2024" 

 

I want to use conditional formatting to highlight I3:AS6 if the result from =DATE($A$1,1,I2) is equal to TODAY(). Meaning "I2" in the above needs to be passed the column letter variable, so that each column in the Conditional Formatting is assessed based on it's column day number.

 

=CHAR(COLUMN()+64) gets me the column letter.

 

Here lies my issue... by replacing "I" with =CHAR(COLUMN()+64) seems to make sense, but then I don't know how to combine it with the row number (frozen as I'm trying to eliminate the need to have 48 conditional formatting rules).

 

This is the best I've come up with in the Conditional Formatting Custom Formula but obviously it's not working: 

=DATE($A$1,1,CHAR(COLUMN()+64)$2)=TODAY()

 

Any thoughts or advice is greatly appreciated.

excel
Formulas and Functions
    • williamgurney's avatar
      williamgurney
      Copper Contributor
      May 23, 2024

      Martin_Angosto 

       

      Thank you! You set me down the correct path!

       

      Here's the end result that works:

      =DATE($A$1,5,INDIRECT(

      IF(COLUMN()<27,CHAR(COLUMN()+64),

      IF(COLUMN()>26,"A"&CHAR(COLUMN()+38),

      CHAR(COLUMN())))&"$"&22))=TODAY()

       

      $A$1 = 2024

      5 = May

       

      IF(COLUMN()<27,CHAR(COLUMN()+64),IF(COLUMN()>26,"A"&CHAR(COLUMN()+38) = Due to the fact that the column numbers are beyond "Z" ("Z" column number = 26, "AA" = 27 (which CHAR returns "["), "AB" = 28 (which CHAR returns "\") and so on) I had to add a condition to identify >26, and subsequently only add 38 (64-26=38) so it would append "A" and the second character for "AA" and beyond.

       

      Again, thank you so much!

Resources