Excel date formatting question....

grebgonebad

Honorable
Jun 15, 2012
102
0
10,640
1
Hi all!

Basically, I'm trying to create a spreadsheet that I can use to list objectives on, and so far I have three columns which are 'what', 'when' and 'status'. I only need it to be this simple.

I also want to have two sets of these tables, one for short term objectives (Anything up to a week) and one for long term objectives (Ranging from a week to a few months).

What I am trying to do is get the 'Status' cell to be highlighted in a certain colour depending on how the 'when' date (Formatted as DD/MM/YYYY) compares to the current date on any given day. The specific time frames I am after are as follows:

Short term table:

If the current date is 3 or more days before the 'when' date, the cell is highlighted in green.
If the current date is 2 or less days before the 'when' date, the cell is highlighted in amber.
If the current date is the same as the 'when' date, the cell is highlighted in blue.
If the current date is after the 'when' date, the cell is highlighted in red.

Long term table:

If the current date is 2 weeks or more before the 'when' date, the cell is highlighted in green.
If the current date is less than 2 weeks before the 'when' date, the cell is highlighted in amber.
If the current date is the same as the 'when' date, the cell is highlighted in blue.
If the current date is after the 'when' date, the cell is highlighted in red.

I hope that I have provided enough information above, if you require any more please ask and I will be more than happy to provide it. I also hope I have explained it well enough for you to understand what it is I am trying to achieve.

Thanks in advance for any help guys!
 
Create a formula next to the cell with the date, which will return 1/2/3 depending on the cryteria you've entered. Assume current date is in A1, your date is in A2, put formula in A3:
Code:
=if(a2<a1-3,0,if(a2<a1-2,1,if(a2=a1),3,4)))
etc, then use conditional formatting based on cell A3 to format cell A2
 
Create a formula next to the cell with the date, which will return 1/2/3 depending on the cryteria you've entered. Assume current date is in A1, your date is in A2, put formula in A3:
Code:
=if(a2<a1-3,0,if(a2<a1-2,1,if(a2=a1),3,4)))
etc, then use conditional formatting based on cell A3 to format cell A2
 
Thread starter Similar threads Forum Replies Date
B Apps General Discussion 2
N Apps General Discussion 1
S Apps General Discussion 5
N Apps General Discussion 2
L Apps General Discussion 5
B Apps General Discussion 4
K Apps General Discussion 1
R Apps General Discussion 1
S Apps General Discussion 1
B Apps General Discussion 1
Z Apps General Discussion 4
V Apps General Discussion 8
P Apps General Discussion 1
K Apps General Discussion 0
grebgonebad Apps General Discussion 7
Q Apps General Discussion 2
unoriginal1 Apps General Discussion 1
J Apps General Discussion 1
C Apps General Discussion 1
L Apps General Discussion 1

ASK THE COMMUNITY