Formatting Dates

[pknivens]

I Have a spredsheet with the birth dates of several people. I wan
these dates to change color when they are 30 days away. I can do thi
now with the [cell value is between =today() and =today()+30
conditional formatting formula but I have to input the current year fo
the birth day, I was wondering is there any way to get the same resul
and input the actual year of the birth day. Ex. If the birth day is o
1 Jan 1984, I want the cell to change color on 2 Dec of every year an
the change back on 2 Jan

 

[Dave O]

Try this: set conditional formatting to Formula Is and this formula:
=TODAY()-(MONTH(B2)&"/"&DAY(B2)&"/"&YEAR(TODAY()))<=30

.... where B2 is the person's birthday (you'll probably need to adjust
that for your use). It essentially does the same calculation you were
doing by rebuilding the birthday and substituting the current year in.

 

[pknivens]

Dave,

Thanks for the help. This works to change the color when it is 30 day
out. However once the date has past I need the the color to change bac
to the original color. With this formula it stays the formatted colo
until the next year. Is there a "Cell Value Is" "Between" "this" an
"that" that would work. I know this may sound simple, but I am jus
learning. Agian thanks for the help

 

[David McRitchie]

Hi pknivens and Dave O.,

Warning: That suggested formula is no good if the birthday is later than today's date.
i.e. a birthdate of 1980-12-15 and a current date of 2006-01-03 will fail
Also the date with slashes is only good for US type of dates so the formula
should be using the DATE Worksheet Function

Also am using an Absolute Column B ($B1) so that the entire row can
be highlighted if wanted. The fact that at column title would be invalid is
immaterial to Conditional Formatting it would be treated as False, the
active cell when entering C.F. can be any cell on row 1 i.e. $B1

Anniversary Date coming up within 30 days. (True or False)
=IF(TODAY()<=DATE(YEAR(TODAY()),MONTH($B1),DAY($B1)), DATE(YEAR(TODAY()),MONTH($B1),DAY($B1))-TODAY(),
DATE(YEAR(TODAY())+1,MONTH($B1),DAY($B1))-TODAY())<=30

For more information on Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm#anniversary

BTW, the subject title does not match the question

 

[pknivens]

Works Great. Thanks alot

 

[David McRitchie]

You're welcome, and it has been added to my Conditional Formatting
page. Both as a formula for days to next anniversary date (birthday)
and as a Conditional Formatting 30 day warning. Thank you.
http://www.mvps.org/dmcritchie/excel/condfmt.htm#anniversary