Conditional Formatting/Functions

**JessicaB517** · 09-21-2012, 11:53 AM

Hello all, I am brand new to this forum and really excited about the potential to learn so much from all of you!!

I am a high school coach and part of my responsibility is to keep track of what the school refers to as "occurrences" when student athletes accrue "points" for certain violations. These points drop off after a certain time frame, for the sake of simplicity, let's say it's after one year. This is an example of what I am looking at:

Date Amount
04/15/2010 0.50
04/19/2010 1.00
05/10/2010 1.00
07/15/2010 1.00
08/03/2010 1.00
09/01/2010 1.00
09/08/2010 1.00
10/14/2010 0.50
11/30/2010 1.00
02/18/2011 1.00
05/09/2011 1.00
06/06/2011 0.50
06/21/2011 1.00
08/30/2011 0.50
08/31/2011 0.50
09/06/2011 0.50
09/13/2011 0.50
09/21/2011 0.50
10/04/2011 0.50
10/10/2011 1.00
10/26/2011 0.50
10/27/2011 0.50
12/12/2011 1.00
02/16/2012 0.50
03/05/2012 0.50
06/27/2012 1.00

I would really like to see the following happen automatically. I would like the 2nd column value to become a negative number after the date in the 1st column has expired by one year, and ideally, I would also like the values in both columns format to a red font to indicate the expiration. It's really not that bad to do it for one student manually, but I have been doing it for several students for several months and I am sure that there is a way to make this happen, although I have been unable to pinpoint the formula.

Your assistance is greatly appreciated!!

**K m** · 09-21-2012, 12:48 PM

It's unclear to me:
I would like the 2nd column value to become a negative number after the date in the 1st column has expired by one year

Which 2nd column value? The original value or the current value?
Explain the problem better

The second part can easily be done using Conditional Formatting

**FDibbins** · 09-21-2012, 01:09 PM

without using VBA to change the contents of your "score" cells, i added a helper column in C, and then used this formula to return the negative values you wanted...

=IF(A2<TODAY()-DATE(1,0,0),-B2,B2)

to change the color to red, us conditional formatting, with this formula for A and B =C2<0, and just <0 for C

let me know if this gives you what you wanted?

**JessicaB517** · 09-21-2012, 01:20 PM

I hope that this illustration helps. I am not very good at expressing what I am trying to do, I am very sorry!!

**FDibbins** · 09-21-2012, 01:49 PM

instead of uploading a pic of your sample data, it would make it much easier for us if you uploaded the actual workbook instead. many who help here are disinclined to retype all of your data for you

**JessicaB517** · 09-21-2012, 01:57 PM

Oh sorry!! That makes sense!!

**BB1972** · 09-21-2012, 02:22 PM

JessicaB517,

You can use the formula:

Please Login or Register  to view this content.

which is basically a true or false argument, which asks "is the date in A2 less than today minus a year". If that's true, the formatting is set to red. Have a go at putting the exact same formula in Cell A2, using conditional formatting, formatting font as red, and see how you get on - you should get the same result as Column C, ie red if the date is more than a year old.