Conditional Formatting or IF using Dates
Solved/Closed
TS
-
Nov 18, 2009 at 11:24 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 12, 2010 at 09:01 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 12, 2010 at 09:01 AM
Related:
- Conditional Formatting or IF using Dates
- Excel conditional formatting if another cell contains specific text - Excel Forum
- If cell contains text then return value multiple conditions ✓ - Excel Forum
- How to clear formatting in excel - Guide
- To change conditional formatting that applies a red fill color to one that applies a green fill color, which of the following can you do? - Excel Forum
- Excel formula to change cell color without conditional formatting - Excel Forum
2 responses
sharpman
Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
183
Nov 18, 2009 at 05:29 PM
Nov 18, 2009 at 05:29 PM
This is how it can be achieved in excel 2007.
Highlght a row that you wish to format (or highlight all the data on that row only)
select 'conditional formatting' from the ribbon menu
Manage rules
New rule
for the first one you need to select the cell that the date is in that you wish to action.
in my case I am using A2 and B2
create the following rules (x3 in any order)
=$B$2>TODAY() then format this any colour you like. this means if th date in cell b2 is later than today format the line with the colour selected.
=$B$2<TODAY() Then format the colour required. This means if the date is before todays date then format the row with the colour selected.
=$B$2 = TODAY() then, again format the colour desired. This means that if the date in cell b2 is todays date the colour selected colour
then create the next rule as follows
=$A$2="" then select the clear/white for the colour. this means if cell a2 is empty then colour the row clear/white. But you must also tick the stop if true box at the end of rule.
what this will do is if cell a2 is blank then the rest of the rules are ignored.
Hope you can understand/decipher that.
hope it helps.
Highlght a row that you wish to format (or highlight all the data on that row only)
select 'conditional formatting' from the ribbon menu
Manage rules
New rule
for the first one you need to select the cell that the date is in that you wish to action.
in my case I am using A2 and B2
create the following rules (x3 in any order)
=$B$2>TODAY() then format this any colour you like. this means if th date in cell b2 is later than today format the line with the colour selected.
=$B$2<TODAY() Then format the colour required. This means if the date is before todays date then format the row with the colour selected.
=$B$2 = TODAY() then, again format the colour desired. This means that if the date in cell b2 is todays date the colour selected colour
then create the next rule as follows
=$A$2="" then select the clear/white for the colour. this means if cell a2 is empty then colour the row clear/white. But you must also tick the stop if true box at the end of rule.
what this will do is if cell a2 is blank then the rest of the rules are ignored.
Hope you can understand/decipher that.
hope it helps.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 12, 2010 at 09:01 AM
Feb 12, 2010 at 09:01 AM
Answer given by sharpman is exactly what you looking for. Read his answer again. He is using today, you surely can change today to sunday and saturday.
Jan 10, 2010 at 09:25 AM
Your answer gave me some light. However, I would lik to take the conditional format a step further by allowing the conditional format to apply using the system date on various dates. I am intending to do the following;
It is a training chart built on date timing such that 3 months before approaching 12 months from the initial training date the cell should flag yellow, 2 months before approaching 12 months from the initial training date the cell should flag red.
Please advise me on how to conditionally format without me having to track the dates but for the system dae to do it for me.
Regards
Ishmael
Jan 13, 2010 at 08:26 PM
So the color in column M is dependent upon the entered date in K.
Feb 12, 2010 at 08:47 AM
I read your answer to a solution using date to in conditional formatting to highting cells. But my problem ois that I have date only in one column, So in short if its sat or sun I want that whole row to appear in another color I mean the entire ROW