Using Different Cells Date For Conditional Formatting?
Jul 31, 2012
I feel like this is a pretty simple question to answer but I cant find an answer to it anywhere or I'm just not skilled enough to understand it. I have two columns D and E that have dates in them. Column D is the purchase date of a computer and column E is how many years months and days its been since it was purchased. I want both columns to be the same color. If the computer is 2 years old or less I want it green, 3-4 yrs old yellow and 4+ red. I have it working for column D but I can't figure out how to apply it to column E.
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
how the scrolling date bar in the attached is achieved. I am not asking for a step by step with code I am just after the basics of how this was achieved and i will research the rest. Most importantly is how to associate the dates with the rest of the cells in the column, as I imagine this is how I am going to fill the cell...The purpose is to add this scrolling date bar to my workbook, also attached.
I looked through the format link: Conditional formatting page on Ozgrid and was unable to figure out what I want to do.
What I want to do is check a range of cells which have dates in them, and then if they are between such and such dates, they will turn a certain background color. But by "such and such" I mean, between the dates entered in two different cells.
So in the range of A1:A10, if any of those cells are between b1 and b2 then apply color1. If any of them are between b2+1 and b3, then apply color2.
If it's not possible tell me but I think it should be as I can do it with conditional formatting. I need to have 16 conditions though. What it is is a chart with projects in rows and dates in columns. The dates often change and I want to rearrange the entire schedule visually just by changing the date in another place. (I change the date in the target cell, what I call b1 above, and b2-10 are calculated based on that date). Hope that's enough information.
I have attached the sample. I need the cells without the employee or without a boss to highlight a color and i also need the date of certification to highlight if it is more than one year old.
I need for coloring "Expected Delivery Date" column corresponding with "Control Date" and "Control Result. But I want to correspond with the cells' last content of "Control Date" and "Control Result" But first of all I want "Control Date" should be flashed 10 days ago of it's date... (it's already done on my excel file)
Then I want "Expected Delivery Date" as green when - "Expected Delivery Date" =< "Control Date" and "Control Result" =Y (Means Control is Ok on due time)
I want "Expected Delivery Date" as red when - "Expected Delivery Date" > "Control Date" and "Control Result" =Y (Means control is ok not on due time)
- "Expected Delivery Date" > "Control Date" and "Control Result" =N (Means control ok is not given, delayed...)
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.
I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting: Cell value is less than or equal to =TODAY()
All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.
In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
I am trying to get a row of cells to highlight a percentage based on a date range
Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.
In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.
Name Start Date End Date Jan Feb Mar
Dale 11/01/14 26/02/14 21 days 26 days
I have attached the spreadsheet for an example : Book1.xlsx‎
I have two conditions setup in Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting. The first is setup for alternate row coloring with this formula inside Conditional Formatting:
Formula is = MOD(ROW(),2)
My Second Condition is
Cell Value is between $P$10 and $Q$10. This sets the font bold and a different color. The two cell values are two dates. I want to change the cells font color and bolding as long as the value is within that date range. It works fine, but for cells that are on the row that is colored the second condition doesn't apply for some reason.
I have set up a spreadsheet with a set of dates occurring in one column.What I would like is for the cell to highlight in yellow if the value is 3-6 months ago more from today's date and then highlight in red if the value is 6 months or more from today's date.I'm using office 2013 if that makes a difference
I have a column contain cell with value of dates. The value is from Vlookup formula. The problem is when I try to do color code to yellow for any past date from current date to all cells that contain the date value in that column, I dont see any change.
The formula I used for past date from current is =today()-1 (so that should be correct). So, I am wondering why none of the dates that past do not change the color thru the conditional formatting I did.
The Conditional formatting formula I used:
"Format only cells that contain" -- > Cell Value -- > less than or equal to -- > =today()-1
I have 2 columns that I need to set conditional formatting for. In one column (D) I have a date of when a person needs to be paid. In the next column (E) I have when the date of payment was or nothing indicating I have still not paid this person. What I need column D to do is have conditional formatting (im assuming this is the right way to do this) color the cell if we are late or if the person has not been paid yet according to the date in the E cells. Here is what I have so far:
(in cell D1) Condition 1: Cell Value is greater then =$E1 Color grey
Condition 2: Cell value is less than =$E1 Color pink
He is where im running into the trouble. Condition 1 indicates that if cell D is greater then cell E then I have paid the person on time or sooner (grey). Condition 2 shows that if cell D is a date before the date in cell E then it turns pink indicating I was late on payment.
What I also need the condition to do is change cell D pink if there is no info in cell E, which is indicating that I still need to pay someone. So I tried a 3rd condition, but it doesn’t coincide with condition 1. I tried:
Condition 3: Cell value is greater then 0 Color pink
What I need the 3rd condition to do is say if cell E has nothing in it then I need cell D to change to pink. Im assuming I need 3 conditions for this to work, but im not to sure.
I have a column of dates in column M, and in A1 I have the formula =TODAY() for today's date. I would like to conditionally format all of column M (up to row 198) so that it turns orange if the date in M is before today and after or equal to 3 working days before the date in M.
I have this =AND($A$1<M2,$A$1>=(WORKDAY(M2,-3))) But it doesn't appear to be working because it's highlighting dates after todays date.
I have two date columns named "start_date" and "Dead_line".
Both the columns are in the date format. Ex: 9/14/13 9:14 AM
i want the third column to be the "status" column.
if sysdate-dead_line < 30 % of (dead_line - start_date), then the status column should become green. if sysdate-dead_line is between 30 % and 70 % of (dead_line - start_date), then the status column should become amber. if sysdate-dead_line > 70% of (dead_line - start_date), then the status column should become red.
First the dead_line - start_date to be converted to hours, then have to do conditional formatting like mentioned above.
I want set a condition in a cell that contains a date , to change to a colour when this date has passed another date by 3 days. E.g. order date received date if the received date has gone over 3 days past the order date i want it to flag up and change colour.
Despite 'Google is my Friend' and lot of test, I got a bug in my formula. I'd like to colorize date between (Today + 30) and (Today + 60) The line with the xlsBetween operator, fail...
[Code] ......
Existing solution : I know how to bypass this issue, but it's really an awfull solution : fill all column with orange, and then the Conditional formating will overrun the orange color when nedeed. Works but I don't like it ...
I would like a date in a cell to flag up in a colour (say, red) once the date has expired by a certain period (e.g 7 days). How can I do this (conditional format).
I need to know if new entries are made within the last 7 days. I do not need the date posted to my viewing just a highlight in my choosing of color. I only want the conditional format to last 7 days then it can go away to normal format. Is this possible or is there an alternative function available to solve same problem?
I am trying to use conditional formatting to highlight items that are either past due or coming due soon. The data to be evualuated is in a report that I extract from another system and run bi-weekly or monthly. The dates included in the report represent the date in which something was last updated, certified, tested, etc. All dates expire one-year from when they were last completed. The colors that I want are as follows:
If something is past due (date shown is more than 1 year old) or due within 30 days = RED.
If something is due within 31-60 days = YELLOW
If something is due within 61-90 days = GREEN
So, if an item on a report is showing it was "LAST UPDATED" on 2/21/2007 it should show up RED because it is due within the next 30 days.
If an item is not expired (less than 1-year old) and falls outside of the defined 90 day window then it should just remain as is with no formatting.
Short Version: I need to find a way to have the date in a cell be used for both a calculation (one date subtracted from another) AND a conditional format (Cell changes color based on the month). Cell format is mm/dd/yy
Explanation: We enter information for each work order on a row in our Master Work Order List. For each new work order we start a new row at the bottom of the sheet.
We calculate each work order's throughput time by having a formula in column F that subtracts the Start Date in column D from the Complete Date in column E.
F#=E#-D#
All date values are entered as mm/dd/yy and the cell format is mm/dd/yy.
At the end of the month, we put an AVERAGE formula into a cell in column H and, for the sources, use all cells in column F that represent work orders that were completed for THAT month. The problem is not all work orders complete in the order they started and some can start in one month but not complete until 2 months later, so we can't just click-drag a bunch of cells in a column. Sorting doesn't work because after we click and drag a bunch of cells and get the average, when we re-sort back to Sort by Work Order Number, the clicked-cells don't move, but the data does.
We end up just looking up and down column E looking for dates from this month and hope we got them all.
So I thought, "Hey, I know, I'll put conditional formats into column F so that if the date in column E starts with 01 (January), the cell in column F will turn Red, and if it's 02 (February), the cell will turn Orange and so on through the end of the year! That way we could just look for a given color each month and are MUCH less likely to get it wrong!
I added conditional formatting using the LEFT(E#,2) formula, hit [Enter] and... nothing.
I thought, "I"ll set the cell formats to TEXT so the LEFT formula 'sees' the month number!"
So I set the cell formats to TEXT and then the conditional format 'sees' the '01' for January and changes color!!
But now the Throughput Time formula (=E#-D#) doesn't work.
I think it's because Excel can DISPLAY a date as mm/dd/yy (due to Date Format), but it actually calculates date information based on a number that REPRESENTS the mm/dd/yy. I read that Excel 'sees' each date as a number representing time passing, not as "mm/dd/yy" and uses that number for calculations.
SO, is there a way to have a cell's date be 'calculatable' and still have it change color based on the month?
I have a column of cells containing dates. I need to change the colour of any cell which is 7 days older than todays date. I would like to leave the header and any blank cells unchanged. Every formula I've tried has changed the enire column, or if I selected a range, the entire range.
I am trying to get conditional formatting to work on this problem but I am getting nowhere fast,
In A1 I have a start date, I want B1 to turn Green if A1 is less that 1 year old, C1 to turn Yellow if A1 is between 1 - 3 years old and D1 to turn red if A1 is more then 3 years old.
I am using Excel 2002. I have a spreadsheet with a list of dates. How can I get conditional formatting to highlight the cell, if the date listed is before the current date.
I have a due date of 30/10/2007 in cell A2, I would like the cells in row 2 to change to the colour green 15 days before the due date (15/10/2007) and then I would like Row 2 to change to the colour Amber 10 days before the due date (20/10/2007).
This is what I was trying to do but it is not working for me:
I was then thinking that I could put a formula in Conditional formatting that says something like If todays date is equal to b2 then Green else do nothing or If todays date is equal to c2 then Amber else do nothing
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
