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

#### cmathys

##### New Member
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!

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Fergus

##### Well-known Member
Something like this:
Book1
ABCD
1HireDateBirthdate3monthReview6monthreview
224/08/200408/01/19743mos24/02/07
313/02/200315/10/19653mos13/02/07
Sheet2

Formula in C2 :
Code:
``=IF(TODAY()-A2>90,"3 mos","")``
Formula in D2 :
Code:
``=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

##### MrExcel MVP
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

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

Replies
11
Views
385
Replies
7
Views
60
Replies
5
Views
382
Replies
4
Views
194
Replies
1
Views
207

Threads
1,136,310
Messages
5,675,002
Members
419,543
Latest member
Casp

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back