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