In Excel Conditional Formatting on Dates

Question asked by user @ Excel forum:- 

How to mark dates on a calendar through conditional formatting?

I have a calendar that I want to mark the dates as per the listed Date Criteria. I thought conditional formatting would be the best way, however can't seem to find a way.

Following is the list of start & finish dates in column A & B

We want conditional formatting to highlight dates that matches Dates Criteria in column D; refer below snapshot

image 1


We need to follow the below steps:

  • Select range A2:B10
  • Click on Home ribbon; from Styles group select Conditional Formatting or use shortcut key ALT + O + D

image 2


  • Click on New Rule
  • Select “Use a formula to determine which cells to format”
  • Enter the formula as =IF(OR(A2=$D$2,A2=$D$3,A2=$D$4),TRUE)

image 3


Main Page


Conditional Format Based on Dates

Find Occurrence of Text in a Column

How to Highlight a row on the basis of Cell

Compare 2 Columns and Return Fill Red if is different

How to check the row and then highlight the first cell of the row

Highlight Cells Tomorrow Excluding Weekend

Conditional Formatting to Mark Dates on a Calendar

How to apply Conditional Formatting  in a Cell before a Particular Character

Highlight the Top 10 Sales through Conditional Formatting

Conditional Formatting for Pivot Tables

Conditional Format Between First and Last Non-Blank Cells






Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.