Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
Caleeco

Conditional Formatting Based on Date Proximity

Recommended Posts

Purpose

When working with a task list that has completion dates, it is useful to have a visual representation for tasks that have been completed, overdue or completed. This handy Conditional Formatting trick with do the leg work for you by indication

  1. When a task is overdue and not been completed
  2. When a task is due within the next 5 days
  3. When a task has been completed

Results

The data set I am working with and the formatted cells are shown below

kb9c3k.jpg

 

To achieve this, 

  • Select your end date data (Cells B2:B14)
  • On the 'Home' Ribbon, click 'Conditional Formatting'
  • Click 'New Rule'
  • Click 'Use a formula to determine which cells to format'

Then set up three rules for each colour as indicated below

 

Green

=D2="Completed"

Yellow

=AND(D2"Completed",AND(B2=TODAY()))

Red

=AND(D2"Completed",B2

If you have any questions, Drop a comment below :D

Caleeco

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...