Conditional Formatting For Dates

We can do conditional formatting in Excel for expiry dates using the Conditional Formatting feature in the Home tab.
For example, the table below lists grocery items and their manufacturing and expiry dates.



And the requirement is to conditional format the expiry dates to highlight those past 6/20/2023. In other words, we aim to highlight the expiry dates of expired items, considering the date 6/20/2023 as the cut-off date.

1: Select the cell range C2:C8 and choose Home – Conditional Formatting – New Rule.



The New Formatting Rule window will open, where we must select the last rule type.



2: Enter the date formatting condition in the field inside the Edit the Rule Description section.

=$C2<DATE(2023,6,20)



And click Format to open the Format Cells window.

3: Select the Fill tab to open it and choose the required background cell color.



And click OK.



Finally, once we click OK in the New Formatting Rule window, it will close, and we will obtain the below output.



The DATE() accepts three arguments, year, month, and day. And it returns the date 6/20/2023. Next, the formula checks each cell date value in the chosen cell range C2:C8, whether the date is older than the specified cut-off date, 6/20/2023.

So, if a date value’s serial number equivalent is smaller than the cut-off date’s serial number equivalent, the condition holds in the corresponding cell. And it gets highlighted in the specified format.

Excel conditional formatting dates within 14 days is possible using the following formula as the conditional formatting rule in the Conditional Formatting feature in the Home tab:

AND(Date Value>(TODAY()-14))

The formula will highlight cells within the chosen cell range that have a date within 14 days of the current date.

Your date conditional formatting is not working, perhaps because of the following reasons:
• The supplied dates are invalid.
• The supplied date values appear as dates, but they are text values.
• The date formatting condition or rule specified in the New Formatting Rule window is incorrect.