Conditional Formatting

[Taryn A]

I have an Excel workbook with a lot of dates that are colored coded as follows

Red indicates that the date in the cell is 13 months past the date entered in the cell

Orange indicates that the date in the cell is 1 year past the date in the cell

Green indicates its within the year of the date entered in the cell.

As the workbook is set up now i have to manually change the color of the cell.

i know there is a formula using conditional formatting that can automatically change the date but I'm not sure what that formula is. I will need this for most of the workbook. Can anyone help?

 

[Taryn A]

sorry to keep clarifying

I now have the spreadsheets and just need to understand all the rules for each colour

I see this in one of your posts
cell A6 = 3/26/10 - RED - 15 months
cell A7 = 4/24/12 - orange - 12 months
cell A8 = 4/17/13 - green - less than 12 months
But that does not align with the spreadsheet

Also you have three dates column E, J & O
How are we testing those ? which dates column are we using

RED, ORANGE & GREEN is mentioned in those rules above,

RED & ORANGE and appears to be required against any of the dates - so thats fine ,
BUT you also have BLUE and YELLOW

so I would like to have the rules for each of those colours and which Dates they need to apply to?

YEAH, at least you were able to get to the spreadsheets! So to give you some background, which I should have done a while ago, the spreadsheet reflects 3 different types of tests that are given to fire fighters, Medical Evaluation, OSHA Questionnaire and Fit Test. They must pass all 3 tests in order to be considered fully cleared (Full Participation). All 3 tests are not necessarily given at the same time hence why you may see different dates across the rows. Each date is accessed separately and should be colour coded based on the colour key using the current date (today’s date). </SPAN>

As you see there are sub headings under each test, i.e. under Medical Evaluations and OSHA Questionnaire there are 4 sub headings titled Med Eval Date, Full, Limited, and Failed. Then you have the Fit Test and under that you will see sub headings Date, Pass or Fail. Lastly, is the heading Cleared with 2 subheadings Yes or No. All these columns are used in determining what colour is applied. </SPAN>

Starting fresh and ignoring all the other posts, the following is the colour code key:</SPAN>


Colour Code Key (this is also at the bottom of the spreadsheet)</SPAN>

Full Participation = Green (Indicates within 1 year or less of today’s date)</SPAN>

Failed, Missing, or Expired = Red (Indicates Medical, OSHA or Fit Test Date has Exceeded 15 months of today’s date)</SPAN>

Out of Date = Orange (Indicates Medical, OSHA or Fit Test Date has Exceeded 12 months, but less than 15 months of today’s date)</SPAN>

Limited Participation = Yellow (Indicates Limited Participation)</SPAN>

Inactive Status = Blue (Indicates College, Extended Injury of Sick Leave, Deployment, Personal Reasons)</SPAN>

The Yellow fill is used for members that have limited participation. The colour rules that apply to those members are Red & Orange and of course Yellow. They would not be coloured Green since Green stands for full participation. That being said, there are different variables regarding the colours since these are 3 separate tests. </SPAN>

An example of a member with Limited Participation would be coloured Yellow under the Med Eval Date (providing the date is within 1 year or less than the current date) with a 1 in the Limited column and coloured Yellow (columns E & G). They also could have limited participation for OSHA which means the OSHA Date column would be coloured Yellow (again providing the date is within 1 year or less than the current date) with a 1 in the Limited column and coloured Yellow (columns J & L). However, they may have passed the Fit Test, so the Fit Test Date would be coloured Green (once again providing the date is within 1 year or less than the current date) with a 1 placed under the Pass column and coloured Green (columns O & P). In the Cleared column a 1 would be placed in the Cleared Yes column (column S), and would be coloured Yellow. Also, the Last, First & MI columns should be coloured to correspond with the Cleared column. In the Limited Participation instance the Last, First & MI should be coloured Yellow since the Cleared column is coloured Yellow.</SPAN>

If any of the 3 test dates are out of date, missing, or expired they would need to be coloured per the Colour Key. For instance, the limited participation member, if the Med Eval Date is expired it should be coloured Red under the Med Eval Date, and under the Limited column where the 1 is placed it should be coloured Red. If it were out of date it would be coloured Orange, but the 1 will still remain under the Limited column but the colour should be changed to match the colour of the Med Eval Date, i.e. Red or Orange.</SPAN>

The above rules pretty much apply to the entire spreadsheet, but obviously using the Colour Key. Basically, whatever colour the date columns are coded for each of the 3 tests the corresponding sub columns should reflect the same colour as the date with a 1 in the appropriate column, i.e. if they passed, are limited, or failed. </SPAN>

Another example would be if a member passed all 3 tests, and all 3 dates are within 1 year or less of the current date they would be coloured Green with a 1 placed in the Full, None and Pass columns under each test, and a 1 placed under the Cleared Yes column and coloured Green. The Last, First and MI would be coloured Green to correspond with the Cleared Yes column. To simplify, whatever colour the Cleared column is coloured is what the Last, First and MI should be coloured for that member. </SPAN>

Again, the member must have a test date for every test in order to be considered fully cleared. So for instance if a member has current test dates for the Med Eval and OSHA, but no test date for the Fit Test then they would be coloured Red with a 1 under the Cleared No column which would correspond with their name. </SPAN>

The bottom line is if any of the dates are coloured Red because they have exceed the 15 months and/or they are missing or they failed any of the 3 tests the member is Not Cleared. In this case, a 1 would be placed under the Cleared No column and coloured Red and their name would be coloured Red to correspond with the Cleared No column.</SPAN>

With regards to the Blue, I’m not exactly sure how to about that. The Blue indicates that the member is in inactive status for various reasons. The colour code key still applies to them. However, we leave them highlighted in Blue so that we know that they are in inactive status so that they are not coloured red. That being said, if any of their dates are expired (which would mean they would be coloured red) the Cleared column should reflect a 1 under No, but should stay coloured Blue. </SPAN>

I’m thinking that in addition to the conditional formatting I’ll need an IF formula so that we can get the other columns to fill in when a 1 is entered? Also, if the Cleared column is going to be the same as the name column, we could probably use an IF formula for that as well. </SPAN>

I hope this makes more sense to you now that you can actually look at the spreadsheet and see the pattern. </SPAN>

Again I really appreciate your assistance with this! Hopefully it’s giving you a bit of a challenge J</SPAN></SPAN></SPAN>

 

[etaf]

taking just small steps at a time , and making sure those rules are correct

so I have colour coded the sections
E:H
J:M
O:Q

And applied the rules for

Red
Orange
Green
Yellow

I have not applied the cleared columns - but that would simply be an IF() statement based on and OR for all the rules used for the above colours

They don't quite match your other sheet - row 17

if you could just check those sections are doing what you need , then we can move onto the BLUE
and also applying to the cleared columns

if you could thoroughly test this sheet - then we can move on or correct - BUT I wanted to do in steps , so we can see each part working and move on from a none working model to add the next part

https://www.dropbox.com/s/snf9djphx96i2gx/Medical Physical ETAF_1.xlsx

 

[Taryn A]

taking just small steps at a time , and making sure those rules are correct

so I have colour coded the sections
E:H
J:M
O:Q

And applied the rules for

Red
Orange
Green
Yellow

I have not applied the cleared columns - but that would simply be an IF() statement based on and OR for all the rules used for the above colours

They don't quite match your other sheet - row 17

if you could just check those sections are doing what you need , then we can move onto the BLUE
and also applying to the cleared columns

if you could thoroughly test this sheet - then we can move on or correct - BUT I wanted to do in steps , so we can see each part working and move on from a none working model to add the next part

https://www.dropbox.com/s/snf9djphx96i2gx/Medical Physical ETAF_1.xlsx

You are awesome! Thank you for doing it so quickly! I'll check it out, and get back with you. I probably won't have time today though. Ugh too much to do and there is just not enough time in the day lol! I'm sure you know how that goes. I'll try and get back w/you as soon as I can. Again, I turly appreciate your help! Once we have it all straight OMG it will make my life so much easier!

 

[Taryn A]

I was so excited, I just had to check out the spreadsheet you sent me, and I’m glad I did! OMG it is awesome! I only have a few things:

With regards to Row 17, I’m not sure why it’s not matching my sheet, but I tested it and it works great! I probably made a mistake on my sheet.

I tested Row 17 by entering 3/11/13 and it did not fill with a colour. It should be Orange since it is exactly 1 year from today.

Also, would it be possible to have the colour fill only in the date column and in whatever column the 1 is located rather than having it coloured all the way across? If you look back at my spreadsheet you will see what I mean. For instance Row 9 should be coloured green in columns E & F, J & K, and O & P with the rest of them remaining white. If there is no date(s) then it should be Red in the date(s) columns and the cell beside each date column would be Red.

Last thing, would it be possible to have the Name columns A:C to match the colour of the Cleared columns? You could use the IF statement for that as well right?

Thank you so much again! I can't wait to apply this to the workbook!

 

[etaf]

Also, would it be possible to have the colour fill only in the date column and in whatever column the 1 is located rather than having it coloured all the way across?

We will need separate rules to do that

Can we assume that
Column Limited will be yellow if a 1 regardless of date
Column fail will be RED if a 1 regardless of date

otherwise you will get a red and a green

RED date if a fail column has 1 - but the the 1 will be green if the date is OK
as shown here

only look at columns
E to H
J to M

where I saw the issue

so
Column E and J can follow the Date colour if it has a 1 in
thats the full column , as we have some dates expired and a 1 in full

see what you think of these sections
OR
you could simply have the column
F = green if a 1
G = yellow if a 1
H = Red if a 1
and not test the date at all
i have fixed the orange as well

https://www.dropbox.com/s/qftfjdgb7474vq8/Medical Physical ETAF_2.xlsx

as i say small steps at a time to get right

Last thing, would it be possible to have the Name columns A:C to match the colour of the Cleared columns? You could use the IF statement for that as well right?

Yes, we would just use the IF to put a 1 or blank in the cleared and then also apply the rule to columns Ato C
come to that once we build up the other rules and get things right

sorry it will take much longer - but hopefully , get the exact answer first

 

[Taryn A]

We will need separate rules to do that

Can we assume that
Column Limited will be yellow if a 1 regardless of date
Column fail will be RED if a 1 regardless of date

otherwise you will get a red and a green

RED date if a fail column has 1 - but the the 1 will be green if the date is OK
as shown here

only look at columns
E to H
J to M

where I saw the issue

so
Column E and J can follow the Date colour if it has a 1 in
thats the full column , as we have some dates expired and a 1 in full

see what you think of these sections
OR
you could simply have the column
F = green if a 1
G = yellow if a 1
H = Red if a 1
and not test the date at all
i have fixed the orange as well

https://www.dropbox.com/s/qftfjdgb7474vq8/Medical Physical ETAF_2.xlsx

as i say small steps at a time to get right

Yes, we would just use the IF to put a 1 or blank in the cleared and then also apply the rule to columns Ato C
come to that once we build up the other rules and get things right

sorry it will take much longer - but hopefully , get the exact answer first

Sorry took so long to get back w/you been slammed! Let me review your response and I'll try and answer your questions today. Thanks!

 

[etaf]

no rush from my point of view, as your very busy, just depends on your urgency for a solution

 

[Taryn A]

Oh I want the solution sooner than later, but that being said, I've taken this long lol! I just have to prioritize and I've had other things that came before this. The sooner I get the solution the easier my life will be regarding this spreadsheet that's for sure. Okay so below is my response to your questions. Hopefully it will make sense.

Can we assume that
Column Limited will be yellow if a 1 regardless of date: Yes. Row 10, columns E & G and J & L both of these columns should be yellow since there is a 1 under the limited column. However, if the date column becomes out of date or expired the limited column should correspond with the date column colour.

Column fail will be RED if a 1 regardless of date: Yes, if there is a 1 in the failed column, the failed and date column colours should be red regardless of the date.

If there is no date in any of the date column(s) then both the date and full columns should be coloured red and the full column will be blank (no 1 will be entered). The same rules apply for the Fit Test section.

Were you just testing row 12 because I noticed it had a 1 in both the full and failed columns? The 1 should be under the failed column only, and should be coloured red to match the date column. Note: There will never be a 1 in more than one column.

otherwise you will get a red and a green

RED date if a fail column has 1 - but the the 1 will be green if the date is OK
as shown here

only look at columns
E to H
J to M

where I saw the issue

so
Column E and J can follow the Date colour if it has a 1 in
thats the full column , as we have some dates expired and a 1 in full: If the date is expired with a 1 in the full column both the date and full columns should be red. Same goes for if the date is out of date but it would be coloured orange. This is exactly what you have.

see what you think of these sections
OR
you could simply have the column
F = green if a 1
G = yellow if a 1
H = Red if a 1
and not test the date at all: I would prefer for the full, limited and failed column colours to correspond with date column colour. Same goes for the Fit Test pass and fail columns; the date column colour should correspond with the pass or fail column colours. Another word whatever the date column colour is the same colour should be used in the column where the 1 is placed. Everything is based on the date column.


I added to your spreadsheet a few examples of the colour blue at the bottom below the formatting. I’m not sure that this will be doable since I have no way of indicating that the person is on extended leave. Maybe you have a suggestion? As for the rules for the blue, the same rules apply except it should remain blue. The only column that is of importance when a row is coloured blue is the Cleared Yes & No Columns. If any of the dates are expired or a 1 is under any of the failed columns then a 1 is placed under the Cleared “No” column and it is coloured blue. If the dates are still within the year or out of date, then a 1 is placed under the Cleared “Yes” column. Does that make sense?

I noticed you have formulas at the bottom of the spreadsheet. I’m assuming these are the formulas you applied? I was curious about the formula, so I turned on show formulas, and saw all the formulas for each row. So I wasn’t sure if the below formulas are the same. The ones that are applied to each row look so much longer. I was like wholly smokes!

AND($E8>DATE(YEAR(TODAY()),MONTH(TODAY())-12,DAY(TODAY())),$F8=1)

AND($E8>DATE(YEAR(TODAY()),MONTH(TODAY())-12,DAY(TODAY())),$G8=1)

OR(E8<DATE(YEAR(TODAY()),MONTH(TODAY())-15,DAY(TODAY())), J8<DATE(YEAR(TODAY()),MONTH(TODAY())-15,DAY(TODAY())), O8<DATE(YEAR(TODAY()),MONTH(TODAY())-15,DAY(TODAY())),Q8=1,M8=1,H8=1)

$E8<DATE(YEAR(TODAY()),MONTH(TODAY())-12,DAY(TODAY()))


https://www.dropbox.com/s/p4ddo1t0bxvigif/Medical Physical ETAF_2 with Blue Colour Exp.xlsx


Thank you so much again for all your help and patience with me!

 

[etaf]

i think! I may have covered the most of the colour coding

I have added a column V and if "inactive" is in that column - then the ROW goes blue - so you could use a column to mark inactive
Maybe you could put inactive into the MI Column or any code meaning inactive and that would turn blue

the cleared Yes ,No

i have added the rules for cleared

"YES" as all green dates and 1 in FULLs and FIT = pass
"NO" any of the red dates and any 1 in any of the three failed columns

not sure what I do with Yellow or Orange in the cleared column ?

needs a lot of testing !!!!!

https://www.dropbox.com/s/v7x07halg93hc3s/Medical Physical ETAF_2 with Blue Colour Exp_etaf_v3.xlsx

 

[etaf]

I have created an updated version here

https://www.dropbox.com/s/3cabhbqdzvfhith/Medical Physical ETAF_2 with Blue Colour Exp_etaf_v4.xlsx

This only conditional formats the column S if green and T if red - the old version change both columns

I have enclosed the data into a table and made the style plain
now you can add members to the bottom and the formulas in S and T will copy and the conditional formatting will also copy down
OR you can insert columns and the formulas in S and T copy down and retain the conditional formatting