Conditional Format

[skylinekiller]

I have a simple spreadsheet and am trying to get rows or cell (which ever one is easier but prefer row) to highlight Red, Yellow, and Green.

When the date in F3 is within Equal to or less than 50 days from H3 I want the row or cell F3 to turn red
When the date in F3 is within Equal to or less than 51-60 days from H3 I want the row or cell F3 to turn Yellow
When the date in F3 is within Equal to or less than 61 or more days from H3 I want the row or cell F3 to stay green


On a different topic. The days the reports are due are different from Foreman and Superintendent. Is it possible to automatically adjust the formula I have in Column F & G?

When I select Foreman, I want the formula in column F to be =H3-50 and G to be =H3-40
When I select Superintendent, I want the formula in column F to be =H3-55 and G to be =H3-45


The most important part right now is the conditional, I appreciate any help. Thank you

[Pepe Le Mokko]

Which XL version are you using?

[skylinekiller]

2010 Verions 14.0.6106.5005 64bit

[mubashir aziz]

Your First part ll be solved by below Conditional Formats

Red =(H3-F3)<=50
Yellow =AND((H3-F3)>50,(H3-F3)<=60)
Green =(H3-F3)>60

but I'm confused with your formulas in cell F3 ? can You explain this more ?
-solution-peformanceTracker.xlsx

[skylinekiller]

The formula in F3 is a due date. this date is 50 days from the close out date in H3 for Foreman, and 45 days from the close out date in H3 for Superintendents.


Ok, after messing with your conditional formatting, I think I know where I confused you.

In column A3, if I select Foreman, I want it to automaticaly use the formula =H3-50 and if I select Superintendent I want it to automatically use the formula =H3-45.

The Both columns F&G should change by 5 days when selecting Foreman or Superintendent. I think I might need to use a TODAY function to calculate that.

Is this possible? Or is it not worth the time.
And thank you very much for the days calculatio. I can most certainly use that.

[mubashir aziz]

There can be simple solution and there must be no need to two column F&G as we can do it in single column by saying A3 is Foreman then H3-45 and if A3 is Super then H3-50. but I'm afraid that if Due date is getting by deducting 50 from H3 then it can never cross 50. But if you are working on Day in your Que column then it can be easily Get. Hope you are getting my point that actually where you want Conditional Format ?

[skylinekiller]

I think I see your point, I don't think I did a good job explaining. The conditional formatting is only for the date due to management. That's just how I set it up, I could use that column, but use a new column and have days left due to managment, sort of like the ones you set off to the right. If we do that would that work?

[mubashir aziz]

let me work on it and attach sample file! just wait

[mubashir aziz]

I think I see your point, I don't think I did a good job explaining. The conditional formatting is only for the date due to management. That's just how I set it up, I could use that column, but use a new column and have days left due to managment, sort of like the ones you set off to the right. If we do that would that work?

RANK RATEE Assigned To Date Assigned Days in your Que Dates due to Mgt Days due to CSS CLOSE OUT
Foreman Jon Doe Jane Doe 1-Jul-12 14 5-Jun-12 15-Jun-12 25-Jul-12
Superintendent Jon Smith Jane Smith 1-Jul-12 14 5-Jun-12 15-Jun-12 25-Jul-12


Just tell me that how a Date Assigned can be 01-Jul-2012 and Daes Due 15-Jun-2012 ? Another thing CLOSE OUT will be fixed and ll change time to time ?
Please try to explain with suitable and real Dates ?

[skylinekiller]

The report goes to different people for review. This is the date that it is assigned to the individual conducting the review. So once the person review the report and forwards it to the next person, the rdate assigned will start over to the date the next person in the review chan received it. Everyone in the review process can take their time, except the report has to be in managements que within 50/45 (Superintendent/Foreman) days of the closeout date. And the report needs to be to CSS by those formula dates. These two dates due to CSS and due to managemnt are to make aware for everyone in the review process
The close out dates are fixed from report to report. Those dates will be manulaly inputted and will not change. Those are the dates the reports should be completed by.

[mubashir aziz]

This formula will work for super. & foreman

=IF(A3="Foreman",H3-50,IF(A3="Superintendent",H3-45,""))

Sky can you give some example by giving Dates in your excel sheet ?

[skylinekiller]

I have made comments in the spreadsheet:

So here, I have a Foreman and a Super… Foremam: the report was assined to Jane Doe on 20 Jun 2012. It has been in her que for 25 days. The report wil close out on 25 August. So if today's date is 50 days or less then the close out date, the cell will turn red. And for superintendent will turn red if today's date is within 45 days of the closeout date. Do we need a cell that has (TODAY) - the Close out date???

[mubashir aziz]

I ve changed th formula and now condiotnal formula check trhe Day in your que! I think it will solve the problem or ???

Condtional Formats.xlsx

[skylinekiller]

The conditional formatting is off, if the closeout is 12 July, then it's late to management, so the cell should be red because it's 45days or less from the close out date

[mubashir aziz]

You can save As this file and hope error ll not come. yes u can delete all un-necessary stuff bcoz I've put formula only on conditon Format.
But I ve dowloaded the same file on my comuter and its working!

[skylinekiller]

I edited my post, the conditional formatting is reflecting the days in que, but it should be red because the reportis aalready late to management. It was due to management 28 May so it's almost 40 days late.

[mubashir aziz]

I edited my post, the conditional formatting is reflecting the days in que, but it should be red because the reportis aalready late to management. It was due to management 28 May so it's almost 40 days late.

Telling you honestly I'm still unable to understand what you actually want
Now management date was 28-May and today its 15-July so its 48 days late !! Am i right ? and how it will close ?

Now I''ve changed the rules like

Green =AND(TODAY()-F3>50,TODAY()-F3<=60)

Please check & confirm
Condtional Formats.xlsx

[skylinekiller]

The closeout will never change. Even if they are late or TODAY is past the closeout the closeout date will stay the same. So once todays date drops 50 days or below, that cell will turn red and never change because the close out date will not change. If todays date is 60-51 days from the closeout date it will turn yellow. Green 61 and up.

Perhaps I have poorly designed this or I am doing a horrible job explaining. You have been extremely helpful and gernerous with your responses. I will understand if you no longer want to assist. It's difficult to go back and forth.

[mubashir aziz]

The closeout will never change. Even if they are late or TODAY is past the closeout the closeout date will stay the same. So once todays date drops 50 days or below, that cell will turn red and never change because the close out date will not change. If todays date is 60-51 days from the closeout date it will turn yellow. Green 61 and up.

Perhaps I have poorly designed this or I am doing a horrible job explaining. You have been extremely helpful and gernerous with your responses. I will understand if you no longer want to assist. It's difficult to go back and forth.

Its okay but you are getting me confsued with close out date! From which date today's date should be compared ?

[mubashir aziz]

So once todays date drops 50 days or below, that cell will turn red and never change because the close out date will not change. .

today's drop ? against which date ? Today or close out ?

[skylinekiller]

Todays date as in the formula TODAY....meaning the current date...If the current date is 50 days or below the listed closeout date, the cell will turn red

[skylinekiller]

Or should I say if the (TODAY/current date) is within 50 days of the closeout date, the "Due to Management" cell will turn red.

[mubashir aziz]

Todays date as in the formula TODAY....meaning the current date...If the current date is 50 days or below the listed closeout date, the cell will turn red

Current Date is 50 days ? From which date we can calculate these days ?
If current date is 25-Jul-12 and close out date is 26-Jul-12 then cell will be red. Now if current date is 26 or 27 Jul then ?

Or should I say if the (TODAY/current date) is within 50 days of the closeout date, the "Due to Management" cell will turn red.

can you explain this ? by comparing two dates ?

[skylinekiller]

Everytime you open the spreadsheet, the conditional format formula should subtract whatever the current(TODAY) date is from the closeout date. See attached spreadsheet

[mubashir aziz]

I hope attach file will solve yoru problem.

[skylinekiller]

Things seem to be working as they should. Thank you so much for your patience and support. I feel like this is a paid support line. It's nice to see folks on here help like this.

I noticed the the dates to the right that have the (-) signs in front of them. Those appear to be how many days are left. Out of curiousity, why do they have the (-) in front of it? Shouldn't the (-) be only if has passed the close out date? I don't need anything done, I am just trying to understant the function. It would make more sense to count down then become negative after the date has passed.

[mubashir aziz]

The payment is just when someone click the start of our post j/k
anyway regarding formula, I used it from your formulas which you used in sheet, so that u'll not get confused. But if you want to change then you can change the formula.
This is just like that if you subtract largest value from smallest value then you ll get reply in minus (-).
e.g 2-5 = -3