# Excel date formatting question....

##### Distinguished
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!

Solution
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

#### Alabalcho

##### Judicious
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

##### Distinguished
Excellent, cheers! Most appreciated!

Replies
1
Views
4K
Question 3f0 error
Replies
11
Views
1K
Replies
2
Views
3K
Replies
5
Views
3K
Replies
1
Views
1K