CODEWITHSUNDEEP
excel
Saiful Islam
Saiful Islam

Reputation: 1

Plan Date /Actual Date

How to do this in Excel? What will be the code??

If Plan Date (P) is less than Today it Turns into Red
If Plan Date (P) is Next day it Turns into Orange
If Plan Date (P) greater than Today Do Noting
If any Actual Date (A) is input then Plan Date (P) Turns into Green

enter image description here]

Plan/Actual

Plan/Actual

Upvotes: 0

Views: 150

Answers (1)

SincereApathy
SincereApathy

Reputation: 675

You can use conditional formatting:

Based on your example assuming top left is cell A1. CTRL + Select B1:D1 & B3:D3, click conditional formatting and new rule. Click "use a formula to determine cells to format" at the bottom of the list and enter the following formulas to the selected range:

=B1<Today()-1 & format to fill red & bold font

=B1=Today()-1 & format to fill orange & bold font

=B2<>"" and format to fill green & white bold font

Use new rule for each formula. Adjust ranges to meet your needs.

Note: Conditional formatting will evaluate condition top to bottom, so if cell meets multiple conditions (ex: date is less than today and actual date is entered) the highest conditional formatting rule will be applied. So make sure your green fill format is at the top.

Upvotes: 1

Related Questions