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

cmathys

New Member
Joined
Feb 23, 2005
Messages
15
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
Joined
Mar 10, 2004
Messages
1,174
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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Feb 23, 2005
Messages
15
Thank you both so very much!!!
I have printed it out and will work on it in the morning.
I appreciate your help!!!
 

Forum statistics

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

Which adblocker are you using?

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
Top