calculate 3, 6 and 12 month reviews and alert to birthdays

[cmathys]

Column A is hire date
Column B is date of birth
If their birthday is this month, I need to change the font to red in column B for that employee.
3 Months after hire, I need column C to read "3 mos."
Every 6 months after the hire date, I need put the date of their upcoming review in Column C. If it is their annual, this also needs to be in red.
For example,

A=08/24/04
B=01/08/74
If it is November, Column C needs to read 3 mos.
If it is February, Column C needs to read 2/24/06 (current year) in black
If it is August, Column C needs to read 8/24/06 (current year) in red

If it is January, column B needs to appear in red

Any help would be appreciated.
Thanks!

 

[Fergus]

Something like this:

Book1
	A	B	C	D
1	HireDate	Birthdate	3monthReview	6monthreview
2	24/08/2004	08/01/1974	3mos	24/02/07
3	13/02/2003	15/10/1965	3mos	13/02/07
Sheet2

Formula in C2 :

=IF(TODAY()-A2>90,"3 mos","")

Formula in D2 :

=IF(MONTH(A2)+6<MONTH(TODAY()),DATE(YEAR(TODAY())+1,MONTH(A2),DAY(A2)),DATE(YEAR(TODAY()),MONTH(A2)+6,DAY(A2)))

both can be copied down.

Conditional Formatting in B2, painted down, Formula is: =MONTH($B2)=MONTH(TODAY()) then set format to red
Conditional Formatting in D2, painted down, Formula is: =MONTH($D2)=MONTH($A2) then set format to red

The conditional formatting doesn't seem to show up, but on mine cells B3 and D3 are red

HTH

 

[Andrew Poulsom]

In C1 enter:

=IF(MOD(MONTH(TODAY())-MONTH(A1),6)=0,DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(A1)),IF(MOD(MONTH(TODAY())-MONTH(A1),3)=0,"3 mos",""))

and format as mm/dd/yy;;@.

With C1 selected choose Format|Conditional Formatting. Condition 1 Formula Is:

=MONTH(A1)=MONTH(TODAY())

Format as red and click OK twice.

For B1 CF Formula Is:

=MONTH(B1)=MONTH(TODAY())

Format as red and click OK twice.

 

[cmathys]

Thank you both so very much!!!
I have printed it out and will work on it in the morning.
I appreciate your help!!!