Excel Question

Fed up talking videogames? Why?
User avatar
Preezy
Skeletor
Joined in 2009

PostExcel Question
by Preezy » Mon Dec 11, 2017 5:10 pm

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)/¯

User avatar
Rex Kramer
Member
Joined in 2008

PostRe: Excel Question
by Rex Kramer » Mon Dec 11, 2017 5:12 pm


User avatar
Rightey
Member
Joined in 2008

PostRe: Excel Question
by Rightey » Mon Dec 11, 2017 5:18 pm

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.

Pelloki on ghosts wrote:Just start masturbating furiously. That'll make them go away.

Image
User avatar
andretmzt
Member
Joined in 2008
Location: Lincolnshire

PostRe: Excel Question
by andretmzt » Mon Dec 11, 2017 5:19 pm

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. :slol: I was just reading out what I had set up at work but I needed to have the yes/no bit...

Last edited by andretmzt on Mon Dec 11, 2017 5:20 pm, edited 1 time in total.
HSH28 wrote:No Last Guardian.
No new exclusive PS4 games.
No longer free MP for PS4.

Microsoft win E3.
User avatar
Preezy
Skeletor
Joined in 2009

PostRe: Excel Question
by Preezy » Mon Dec 11, 2017 5:19 pm

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

User avatar
lex-man
Member
Joined in 2008
Contact:

PostRe: Excel Question
by lex-man » Mon Dec 11, 2017 5:31 pm

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")


Return to “Stuff”

Who is online

Users browsing this forum: Alvin Flummux, Lagamorph, massimo, Snowcannon and 56 guests