Formatting Dates
 

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


--
pknivens
------------------------------------------------------------------------
pknivens's Profile: http://www.excelforum.com/member.php...o&userid=27675
View this thread: http://www.excelforum.com/showthread...hreadid=471898

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.

Dave,

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


--
pknivens
------------------------------------------------------------------------
pknivens's Profile: http://www.excelforum.com/member.php...o&userid=27675
View this thread: http://www.excelforum.com/showthread...hreadid=471898

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...tm#anniversary

BTW, the subject title does not match the question
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave O" wrote in message oups.com...
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.

Works Great. Thanks alot.


--
pknivens
------------------------------------------------------------------------
pknivens's Profile: http://www.excelforum.com/member.php...o&userid=27675
View this thread: http://www.excelforum.com/showthread...hreadid=471898

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...tm#anniversary

"pknivens" wrote ...
Works Great. Thanks alot.