Sign-in / Sign-up
Your question
Solved

Excel date formatting question....

Tags:
  • Apps
  • Formatting
  • Excel
  • Spreadsheet
Last response: in Apps General Discussion
January 21, 2014 1:16:14 AM

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!

More about : excel date formatting question

Best solution

January 21, 2014 9:13:35 AM

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:
  1. =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
Share
January 21, 2014 12:27:18 PM

Excellent, cheers! Most appreciated!
m
0
l