[Solved] Conditional Formatting of Cells based off of date and time criteria
Hello,
Here is what I am trying to do.
I have three cells right now:
Date of incident
Due Date
Date completed
What I would like to do is the following:
When they enter the date of the incident. There are specific time frames based on the incident. I have each incident type on their own tab.
So, in this particular instance, you have 30 days to complete the item.
Date of incident: 1/1/2015 (A column)
Due Date: 1/30/15 (B column)
Date completed: 1/20/15 (D column)
What I would like to do, if possible. Is when they add the date of incident in say cell A3, cell B3 would automatically add the 30 days. Which I have working using the:=$A3+30 That part works. I do not need to worry about weekends or holidays since those are counted in this process.
What I would like to do is have it so when you enter the date of the incident into the A3 box or any of those cells in the "A" column, the 30 days is added but the cell in the "B" column turns say green. After 15 days the same cell turns yellow, then 3 days to do date it turns red.
However once the date is entered under Date Completed, all of the coloring goes away. At this point it doesn't matter if the item was late or not. So I would like to remove all of the coloring.
I also have a cell A1 that is putting in the current date when you open the file.
So is this possible within excel?
Thanks for any and all advice in advance.
Hello,
Here is what I am trying to do.
I have three cells right now:
Date of incident
Due Date
Date completed
What I would like to do is the following:
When they enter the date of the incident. There are specific time frames based on the incident. I have each incident type on their own tab.
So, in this particular instance, you have 30 days to complete the item.
Date of incident: 1/1/2015 (A column)
Due Date: 1/30/15 (B column)
Date completed: 1/20/15 (D column)
What I would like to do, if possible. Is when they add the date of incident in say cell A3, cell B3 would automatically add the 30 days. Which I have working using the:=$A3+30 That part works. I do not need to worry about weekends or holidays since those are counted in this process.
What I would like to do is have it so when you enter the date of the incident into the A3 box or any of those cells in the "A" column, the 30 days is added but the cell in the "B" column turns say green. After 15 days the same cell turns yellow, then 3 days to do date it turns red.
However once the date is entered under Date Completed, all of the coloring goes away. At this point it doesn't matter if the item was late or not. So I would like to remove all of the coloring.
I also have a cell A1 that is putting in the current date when you open the file.
So is this possible within excel?
Thanks for any and all advice in advance.
Last edited:
