GRcade

Hi guys, anyone know if the below is possible, and if so, how I'd go about doing it:

Column A has a bunch of dates in it (test completion dates) and I want to create something that automatically tells me if the test completion date is more than year old, say by marking it red or something.

¯\(°_o)/¯

Almost certainly

https://www.extendoffice.com/documents/ ... -than.html

Yeah, you can do it, dates correspond to a number. You can convert your column of dates into a number format, and then just do a conditional format to highlight all cells that are less than that number. Today's date is "43080 " 1 year ago would be "42715".

Edit: Rex's answer is simpler as you don't need to convert your dates so just do that.

Not exactly what you are asking but I think if you did something along the lines of the following in B column (assuming all the dates are in A column):

=IF(A1="","",IF(A1-TODAY()>365,YES,NO))

What you'd get is a column where if it less than a year old you'd get a NO result, and if it were greater it would say YES. You can the use conditional formatting to get Excel to colour YES in red to highlight it.

First part of that function is to keep the cell blank if there is no date in the adjacent column. Bit needless perhaps but I like to put it in anyway. The second part has the basic premise, but without being on a computer to check the formula, I can't tell if the way it is written will say yes or no when a test is over a year old.

I can check later when I'm in work.


Edit: or you can use conditional formatting in the first place. I was just reading out what I had set up at work but I needed to have the yes/no bit...

Ok thanks guys I'll have a bash with those methods and see what works.

If you set a field to =TODAY() you can use conditional formatting. Select "Home->Conditional Formating->New Rule

Then choose "Format only cells that contain

You need Cell value less than or equal to then set the value to equal the cell with todays date in -365.

This if function may also be useful:

=IF(A4<(TODAY()-365),"OLD","NEW")