Conditional Formatting Help Needed

[TMILJVIPM]

Good Afternoon All,

I'm in need of some help on a conditional formatting issue I'm having. What I have is a employee training tracker that I need help with and I would like to use the conditional formatting tool with the green light, yellow light, and red light format. Here is an example of what the spreadsheet with look like:


[TABLE="width: 115"]
<colgroup><col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2645;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2816;"> <tbody>[TR]
[TD="class: xl1410, width: 74, bgcolor: #F2DCDB"]Date Training Taken[/TD]
[TD="class: xl1410, width: 79, bgcolor: #F2DCDB"]Training Date Expiration[/TD]
[/TR]
[TR]
[TD="class: xl1411, width: 74, bgcolor: transparent"] 1-1-18[/TD]
[TD="class: xl1412, width: 79, bgcolor: transparent"] 1-1-19[/TD]
[/TR]
</tbody>[/TABLE]


What I need help on is in the training date expiration column. When the training date is expired I would like the red light to show up, when the training date is within 60 days of the expiration date for the yellow light to appear, and when it's not within the 60 day condition nor past expiration then the green light appears.

I have a similar spreadsheet I use for cost tracking that does this but I'm having difficulties getting this done with date ranges. Also my cost tracking tool is set up like this so it has 3 columns and the remaining labor cost is the cell with the green, yellow, and red light........ it just didn't copy and paste that onto here.

[TABLE="width: 240"]
<colgroup><col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3441;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3669;"> <col width="120" style="width: 90pt; mso-width-source: userset; mso-width-alt: 4266;"> <tbody>[TR]
[TD="class: xl1410, width: 97, bgcolor: #CCC0DA"]EST. LABOR COST[/TD]
[TD="class: xl1410, width: 103, bgcolor: #CCC0DA"]ACTUAL LABOR COST[/TD]
[TD="class: xl1410, width: 120, bgcolor: #CCC0DA"]REMAINING LABOR COST[/TD]
[/TR]
[TR]
[TD="class: xl1412, width: 97, bgcolor: transparent"]$167,773.00[/TD]
[TD="class: xl1419, width: 103, bgcolor: transparent"]$152,421.96[/TD]
[TD="class: xl1411, width: 120, bgcolor: transparent"]$15,351.04[/TD]
[/TR]
</tbody>[/TABLE]

Any help is greatly appreciated.

Thanks

 

[Marcílio_Lobão]

TMILJVIPM, Good evening.

You already know the logic for this.

This time, instead of using multiple columns, you will use the "Training Date Expiration" column and the current date through the TODAY() function.

Scenario:

C1 --> Training Date Expiration

Rule 1
Formula --> =C1-TODAY()>60
Format Fill Color --> GREEN

Rule 2
Formula --> =AND(C1-TODAY()>0, C1-TODAY()<=60)
Format Fill Color --> YELLOW

Rule 3
Formula --> =TODAY()>=C1
Format Fill Color --> RED

Please, try to use this and tell us if it worked as you want.

I hope it helps.

 

[DRSteele]

Create this CndFrmt rule for a cell that contains a date:
Format cells based on their values
Format style: icon sets
Green: select Formula as the Type, select >= , in Value put this =TODAY()+60
Yellow: select Formula as the Type, select >= , in Value put this =TODAY()

 

[TMILJVIPM]

TMILJVIPM, Good evening.

You already know the logic for this.

This time, instead of using multiple columns, you will use the "Training Date Expiration" column and the current date through the TODAY() function.

Scenario:

C1 --> Training Date Expiration

Rule 1
Formula --> =C1-TODAY()>60
Format Fill Color --> GREEN

Rule 2
Formula --> =AND(C1-TODAY()>0, C1-TODAY()<=60)
Format Fill Color --> YELLOW

Rule 3
Formula --> =TODAY()>=C1
Format Fill Color --> RED

Please, try to use this and tell us if it worked as you want.

I hope it helps.

It worked, thanks for your help!

 

[Marcílio_Lobão]

TMILJVIPM, Good afternoon.

Glad to have helped you.

Thanks for the feedback.