Forum Discussion

sophigm's avatar
sophigm
Copper Contributor
Mar 29, 2025

Conditional Formatting Comparing Dates

I am attempting to create a formula that formats a cell based on whether the date (contained in the cell) is before the secondary date (on another sheet). This second date will need to be changed via a cell. 

=(B2:E24)<'Read Me'!$C$5

This is the formula I am currently working with. I have listed the ones I used previously below. All of these format any cell that is not empty as meeting the criteria for formatting. 

The first set of values is the cell containing the date that needs to be compared, while the 'Read Me'!$C$5 includes the date it should be compared to. 

=(B2)<'Read Me'!$C$5

 

Let me know if you need any more information. 

 

excel
Excel on Mac
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Mar 31, 2025

    Select B2:E24. B2 should be the active cell in the selection

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Use a formula to determine which cells to format'.
    Enter the formula

    =AND(B2<>"", B2<'Read Me'!$C$5)

    Click Format...
    Activate the Fill tab.
    Select a fill color.
    Click OK, then click OK again.

Resources