Solved Excel; cell auto fill colour change by date

November 10, 2012 at 05:13:37
Specs: N/A

Hi, I'm in the military and I have a lot of guys under me. I'm creating (or trying to) a excel spreadsheet to capture the key requirements and ensure I do them the best service.

Microsoft Office Excel 2007

There are a number of columns with various dates as achieved by the individual. I would like these columns to fill colour change at certain time posts;

Green = anything upto 3 months before the cell date
Yellow = 3 months before the cell date
Red = On that cell date and anything past

A one off column, date of birth, anyone under the age of 18 colour fill pink. Any one 18 and over no fill.

Any and all help would be appreciated - thank you in advance


See More: Excel; cell auto fill colour change by date

Report •


✔ Best Answer
November 10, 2012 at 09:25:48

So, if I understand you correctly, we need only concern ourselves with
Column B the Annual fitness test Date. Correct?
Column C has not impact on what you want to do?

If yes, then try this with your data as shown:

Conditional Formatting 2007

There are Three Rules we will need to apply
and they must be in the correct order.

First:
1) Select your cell or range of cells, IE B2:B4
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=DATEDIF($B2,TODAY(),"m")<9

6) Click on the Format button
7) Select the Fill Tab
8) Select a Green color
9) Click OK
10) Click OK

Second:
1) Select your cell or range of cells, IE B2:B4
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=DATEDIF($B2,TODAY(),"m")>=9

6) Click on the Format button
7) Select the Fill Tab
8) Select a Yellow color
9) Click OK
10) Click OK

Third:
1) Select your cell or range of cells, IE B2:B4
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=DATEDIF($B2,TODAY(),"m")>=12

6) Click on the Format button
7) Select the Fill Tab
8) Select a Red color
9) Click OK
10) Click OK

See how that works.

MIKE

http://www.skeptic.com/



#1
November 10, 2012 at 06:37:45

Read this How-To:

http://www.computing.net/howtos/sho...

Then post a small sample of your spreadsheet,
only need a few columns and rows, with any headings.
change any data that may be sensetive.

MIKE

http://www.skeptic.com/


Report •

#2
November 10, 2012 at 06:55:19

The row has the individuals name.
The column for example has is the military fitness test.
How do I make that column change cell fill automatically green / yellow / red as previously described ?

Report •

#3
November 10, 2012 at 07:14:28

Without seeing how your data is arrainged,
try this:

Green = anything upto 3 months before the cell date: =DATEDIF(A1,TODAY(),"M")<=3
Yellow = 3 months before the cell date: =DATEDIF(A1,TODAY(),"M")>3
Red = On that cell date and anything past: =DATEDIF(A1,TODAY(),"D")<=1

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
November 10, 2012 at 07:33:41

Just figuring out the <pre icon & will be done soon

Report •

#5
November 10, 2012 at 07:51:52

     A         B                               C
1   Name     Annual fitness test          Annual report
2   Tom      1-Nov-11                     30-Nov-11            (last date achieved)
3   Sue      7-Dec-11                     31-Oct-12
4   Fred     9-Oct-12                     1-Nov-11


So how do I automatically change the cell colour fill with these requirements;

On the cell date and up to 9 months from the cell date - auto fill cell green (ie, well in date, no need for an action)

9 months after the cell date and up to 1 day before the cell date reaches 12 months - auto fill cell yellow (ie, I've got 3 months to action annual requirement)

12 months and further from the cell date - auto fill cell red (ie, I've missed annual req)

Please, you might literally have to spell it out, boxes to go to etc

Thank you for your help

Ps; I figured out how it use the <pre icon - yeah to me, self victory one


Report •

#6
November 10, 2012 at 08:20:33

Glad you figured out how to post here.

Please understand that I have no clue what it is your trying to do with your
spreadsheet, so I'm going to need some clarification on what means what and
where you want the colors to appear.

A learning experience for both of us.

Your data looks like:

     A         B               C
1) Name    Test            Report
2) Tom     1-Nov-11        30-Nov-11
3) Sue     7-Dec-11        31-Oct-12
4) Fred    9-Oct-12        1-Nov-11

Is this ALL you have or do you have additional columns of Dates?

What is the relationship between the Dates?

Tom took his Annual Fitness Test on 1 Nov 11, and the Report is Dated 30 Nov 11
29 days later.

while

Sue took her Fitness Test on 7 Dec 11 but here report isn't made until 31 Oct 12
that's 329 days later.

I take it that Fred has just recently taken his Annual Fitness Test, but
no Report has yet been made for this test, as the Date of his Report was
last year.

Am I close?

On cell date and up to 9 months from cell date - auto fill cell green (ie, well in date)

What DATE are we dealing with (Cell # ) or Range of Dates.

9 months after cell date - auto fill cell yellow (ie, I've got 3 months to action annual)
12 months and plus from the cell date - auto fill cell red (ie, I've missed annual req)

Same question as above

To use your own words: you might literally have to spell it out, boxes to go to etc

MIKE

http://www.skeptic.com/


Report •

#7
November 10, 2012 at 08:43:03

Ok,

In my 'team' of guys there are certain annual requirements that need to be met, both from the individual and by myself their 'boss'.

So for example 'Tom' has to do his fitness test once a year, I will record that date which he achieved a pass in a cell (row Tom, column fitness test) in my spreadsheet sending that cell fill colour green as a quick identifier that he is in date fitness.

9 months from the date he completed his fitness test and the recorded date, I want that cell to turn from green to yellow. Showing me that 'Tom' has 3 months before he goes out of date for his fitness test. Therefore a quick friendly email or reminder in the corridor - get to the gym buddy you need to get in date fitness test.

12 months from the date in that cell I want the cell fill colour to change from yellow to red. Showing 'Tom' is out of date for his fitness test. Ie, I go to the barracks provide a good military telling off and send him to the gym with my boot. This cell will now stay red until he comes to me with his pass certificate.

When I receive a pass certificate from 'Tom' I will place the date into that cell, thus starting the process all over again.

With the reports, there is no relationship between fitness test and other people.
It is a reminder when I gave that individual their report, goes to yellow from green 9 months since the last report meaning I've got 3 months to write a report for that person. And to red from yellow 12 months from the date in that cell, meaning that person has not had a report for a year and I've not done my job properly.

There is no relationship between Tom/Sue/Fred. These are individual actions, ie each one of them does a fitness test all at different points through out the year. Each of the receive a report from me at different points of the year.
I'm creating this so I quickly identify amongst a thousand other jobs that my people are being looked after as best I can.

As you can imaging, I have a big 'team' and yes other columns, but they have no bearing on each other. They are effectively single actions that need to be achieved at different timelines with no relevance to another column or person.


Did this clarify?

Regards Will


Report •

#8
November 10, 2012 at 09:25:48
✔ Best Answer

So, if I understand you correctly, we need only concern ourselves with
Column B the Annual fitness test Date. Correct?
Column C has not impact on what you want to do?

If yes, then try this with your data as shown:

Conditional Formatting 2007

There are Three Rules we will need to apply
and they must be in the correct order.

First:
1) Select your cell or range of cells, IE B2:B4
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=DATEDIF($B2,TODAY(),"m")<9

6) Click on the Format button
7) Select the Fill Tab
8) Select a Green color
9) Click OK
10) Click OK

Second:
1) Select your cell or range of cells, IE B2:B4
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=DATEDIF($B2,TODAY(),"m")>=9

6) Click on the Format button
7) Select the Fill Tab
8) Select a Yellow color
9) Click OK
10) Click OK

Third:
1) Select your cell or range of cells, IE B2:B4
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=DATEDIF($B2,TODAY(),"m")>=12

6) Click on the Format button
7) Select the Fill Tab
8) Select a Red color
9) Click OK
10) Click OK

See how that works.

MIKE

http://www.skeptic.com/


Report •

#9
November 10, 2012 at 09:58:42

Brilliant - thank you very much for all you patience & help

Report •

#10
November 10, 2012 at 10:04:50

I just reread your last post and I must have missed this on my last reading:

When I receive a pass certificate from 'Tom' I will place the date into that cell, thus starting the process all over again.

Are we measuring the 9 Months from Column C, Annual report?

In other words,
is it 9 months from the Date he Completed the test,
Column B Annual fitness test
or
is it 9 months from the Date you received the Report
Column C Annual Report

MIKE

http://www.skeptic.com/


Report •

#11
November 10, 2012 at 13:38:42

No it's from the fitness test. The code/formula you provided works at treat.
I've populated and tested with variations.

Smally one;
What would the formula be if I wanted 'clear' fill if text entered into cell?


Report •

#12
November 10, 2012 at 14:22:26

Example; formatting for cell B2, if any date entered into B2, clear fill

Report •

#13
November 10, 2012 at 15:39:03

Sorry, I don't understand what you want.

Is this in relation to the Conditional Formatting
or is it a new question?

MIKE

http://www.skeptic.com/


Report •

#14
November 10, 2012 at 16:33:34

No it's the same spreadsheet.
I have adjusted your formulas to most my columns.
The final column is 'Good Conduct Awards'
So, what I was looking to achieve, if 'Tom' has recieved a good conduct award I would place the date awarded in that cell (B2 for example). Once I've placed a date in that cell, regardless of what the date is, I would like that cell to auto fill/format "b2" cell clear.
I don't know the code/formula for;
If cell b2 has a or any date, format cell clear.

Report •

#15
November 10, 2012 at 19:26:18

I'm not sure what you mean when you say "Format cell clear"
Does it have a color in it by some other process?

MIKE

http://www.skeptic.com/


Report •

#16
November 11, 2012 at 02:17:13

Yes, I manipulated your formula to fill the cell a colour from the date placed in another cell (all works well - thanks), but now I want to be able to clear fill that cell when I place any date in it.

Eg

Fours years of good conduct from joining (joining date goes into a2) the forces 'Tom' can receive his first good conduct award (b2).
So the formula I used sent b2 yellow at the 3years 9months point (meaning I have to raise the required paperwork), and red at the 4 year point (meaning I've missed the point to raise the paper work and to do it now).
Once he gets his good conduct award I will place that date into b2. I then want b2 to auto change from yellow or red to clear just sowing date & no colour fill.

But I don't know the formula for; if date entered in cell ...

Does that clarify?


Report •

#17
November 11, 2012 at 05:43:50

Ah ha,

I've figured it out.
Slow time of course.
By going to;
Conditioning Format - New Rule - Format Only Cells That Contain -
(Drop down menu) - No Blanks
Then select 'clear' on format.

Dahdaaa, magic

Thanks for help buddy. All the best to you & yours.


Report •

#18
November 11, 2012 at 06:23:32

Thank you for your service.

Happy Veterans Day to you and to all that serve with you.

DerbyDad03
USCG 1974 - 1980

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#19
November 11, 2012 at 06:48:53

You seem to be doing a lot of work with Conditional Formatting.

You might want to consider not using so many dates,
when you can use one to do the work.

For example, you have a Date of Entry into the service in cell A2
In B2 you have the date he can receive his First Good Conduct award,
which is 4 years from Date of Entry.

So you actually don't need a date in B2 you can leave it blank.

We can use the Date in cell A2 to do the calculations

Just use Conditional Formatting to color B2 by using A2 as the formula reference, something like:

1) Select your cell or range of cells, IE B2:B4

5) Enter the formula:

=DATEDIF($A2,TODAY(),"m")<9

This way B2 is colored by the results from the calculation done on A2

Then when Tom gets his award, you enter the date in B2, which means
that B2 is no longer empty, so a simple Rule of:

=B2<>""

With no color as the Top or First rule and you should be good to go.

You will probably need a different =DATEDIF() formula for working with 3 years and 9 months.

MIKE

http://www.skeptic.com/


Report •


Ask Question