Solved

# Conditionally format dates in Column A

Posted on 2015-02-12
Medium Priority
67 Views
Dear Experts:

Column A of the current worksheets has entries with dates

I would like to apply conditional formatting to these dates with the following requirements:

If the date in Column A is within the next 10 days (from now) the date should get red shaded...
if the date in Column A is today the date should get yellow shaded ...
... and as soon as the date in Column A is in the past the Shading should change to green.

Help is much appreciated.

Thank you very much in advance

Regards, Andreas
0
Question by:AndreasHermle

LVL 24

Assisted Solution

Phillip Burton earned 800 total points
ID: 40605769
The second one is:

=int(a2)=int(now())
0

LVL 24

Assisted Solution

Phillip Burton earned 800 total points
ID: 40605773
The first one is

=and(int(A2)>int(now()),int(A2)<=int(now())+10)
0

LVL 85

Accepted Solution

Rory Archibald earned 1200 total points
ID: 40605775
If they are just dates (no times) then the conditions are, in order:

Cell Value <TODAY()
Cell Value =TODAY()
Cell Value <=TODAY()+10

and make sure to check the Stop If True option for all of them.

Note: if you have blank cells, you will need to use a formula for the first condition using:
=AND(\$A1<>"",\$A1<TODAY())
assuming applied to A1 onwards.
0

LVL 24

Assisted Solution

Phillip Burton earned 800 total points
ID: 40605779
The third is

=int(a2)<int(now())
0

LVL 34

Expert Comment

ID: 40605862
You only need to specify two of the conditions, the third can be the default hard format colour of the cell.
0

Author Closing Comment

ID: 40607796
Dear all,

both solutions work, but a Rory' is the most simple, I guess. Anyway, thank your very much for your great and overwhelming support. I really appreciate your professional expertise. Regards, Andreas
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
###### Suggested Courses
Course of the Month9 days, 14 hours left to enroll