Conditional formatting with index & match functions

[samc2696]

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 actually managed to create a formula that finds 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))

But when I put this into the conditional formatting box it does not format the corresponding cell.

Any help would be greatly appreciated.

Thank you very much

Sam

p.s. I know a workbook or picture would help but I am not sure how to do that so any help there would be appreciated too!

 

[Eric W]

If your days (1-31) are in column B, and your months are in D3:AA3, then select columns D:AA, click Conditional Formatting > New Rule > Use a formula > and enter:

=AND($B1=DAY(TODAY()),EOMONTH(D$3,0)=EOMONTH(TODAY(),0))

 

[samc2696]

Hi Eric,

That did not work for some reason - but nonetheless, I have worked out an alternate solution.

Thanks
Sam