CODEWITHSUNDEEP
exceldateconditional-formatting
alwbtc
alwbtc

Reputation: 29445

How to do conditional formatting regarding dates in excel

I have a table:

enter image description here

I want cells which top cell (B1, C1) is older than corresponding date value (column D) to be filled with red, as shown below:

enter image description here

Here C3 needn't be red because 15 Feb is in February. How can I do this with conditional formatting?

Upvotes: 1

Views: 337

Answers (2)

Arun Rana
Arun Rana

Reputation: 1

  • Use Conditional Formatting.
  • Use a formula to determine.
  • Enter the formula
=DATE(YEAR($D2),MONTH($D2),1)>DATE(YEAR(B$1),MONTH(B$1),1)

Upvotes: 0

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

Use Conditional Formatting, Use a formula to determine... and enter the formula

=DATE(YEAR($D2),MONTH($D2),1)>DATE(YEAR(B$1),MONTH(B$1),1)

(or the shorter =EOMONTH(B$1,0)<EOMONTH($D2,0) as suggested by pnuts),

enter image description here

and Applies to

=$B$2:$C$4

enter image description here

The formula gets rid of the day in each of the two cells to be compared, keeping only the months/years. It also uses suitable relative/absolute indexing for the matrix. I have used both, each with different formatting, to test them at once.

Upvotes: 2

Related Questions