How to change color if 1 month old?

  • Thread starter Thread starter StoneHalo
  • Start date Start date
S

StoneHalo

Hi,

I have a spreadsheet that contains a column of dates. I would like
these dates to change color if they are greater than or equal to 1
month old.

Does anyone know the formula to do this?

I highlight the column and click on 'Format -> conditional formatting'
but have no idea what formula to use!

Thanks
 
I have a spreadsheet that contains a column of dates. I would like
these dates to change color if they are greater than or equal to 1
month old.
Click to expand...

Could you settle for 30 days? Condition 1 -- Formula Is

=$A$1<TODAY()-30

HTH,
Andy
 
My date column is ColumnC
=C2<=TODAY()-(DATEVALUE("1/"&MONTH(TODAY())&"/2003")-DATEVALUE("1/"&MONTH(TO
DAY())-1&"/2003"))
watch for the line warp in the mail
all should be in one line.
my system date format is dd/mm/yy
if yours is mm/dd/yy
then change DATEVALUE part accordingly
HTH
Cecil
 
Cecil,
I too am interested in this information, I tried your formula, but it
gives me errors specifically related to the brackets. Any clues?

Bobbie
 
If you are using US date format

=C2<=TODAY()-(DATEVALUE(MONTH(TODAY())&"/1/2003")-DATEVALUE(MONTH(TODAY())-1
&"/1/2003"))

although it probably better to use something like

=C2<=TODAY()-(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-DATE(YEAR(TODAY()),MONTH(
TODAY())-1,1))

since the first formula has a static year

of course you can shorten the formula to

=C2<=TODAY()-DAY(DATE(YEAR(TODAY()),MONTH(TODAY()),0))

of course I'd rather go with Andy's suggestion and always used a fixed
number (30 days)
 
Hi,

=C2<=TODAY()-DAY(DATE(YEAR(TODAY()),MONTH(TODAY()),0))
Click to expand...

Also:
=C2<=TODAY()-DAY(TODAY()-DAY(TODAY()))

The same (but with previous month days number ADDED to C2) which I consider
easier to understand (personal taste) :
=C2+DAY(TODAY()-DAY(TODAY()))<=TODAY()

Regards,

Daniel M.
 
Back
Top