Issue a reminder

T

Tendresse

I have a coulmn for recording children's dates of birth. Is there a way to
get the cells to change colour 2 weeks prior to when the birthday is due? Or
any other way to remind the user of each date 2 weeks prior?
Many thanks
 
S

Shweta Srivastava77

Yes, It can be done in excel automatically by writing a macro, which gets
enabled everytime you open your work sheet.

Following procedures should be followed:

1. List down the birth dates in one column (which would be available with
you as you said)

2. Go to Tools and select MACRO and within it RECORD NEW MACRO and fill out
the name of the macro and other information as asked.
(After pressing ok you can see a small window having STOP & RELATIVE
REFRENCING button.) It means MACRO is ready to record the steps you are going
to do next..

3. Select the whole column with birth dates and go to FORMAT Option and
choose conditional formating.
Under Condition1
select CELL VALUE IS (1st box)
then select EQUAL TO (2nd box)
then write (in the 3rd blank box) =TODAY+14 (this will return the date after
2 weeks from the current date)
and under format give some colour to highlight.

This way it could help you getting the birth dates highlighted before 2 weeks.

Shweta Srivastava
 
S

Stefi

Select column of dates of birth!
Formats/Conditional formatting, choose formula for 1.condition
Enter this formula in formula field:
=AND(TODAY()>=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))-14,TODAY()<=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)))
Select a color in the Pattern tag!
Press OK!

Regards,
Stefi



„Tendresse†ezt írta:
 
S

Shweta Srivastava77

It is giving error..Please elaborate the formula to make me understand.

Shweta Srivastava
 
S

Stefi

I have no idea why it gives you an error, for me it works. The explanation is
very simple: if today's date falls between the birthday and the day 14 days
before the birthday, then it is in the reminder period. The birthday in the
current year is calculated from year of today's date and from the month and
day of the date of birth.

Regards,
Stefi


„Shweta Srivastava77†ezt írta:
 
D

David Biddulph

You're more likely to get help if you tell the group *which* error you're
getting.
 
S

Shweta Srivastava77

Am sorry, It is not giving any error actually I missed closed bracket ")"
while copying the formula that's why it was giving problem.

But, Still am not able to get the desired output, as nothing gets
highlighted after giving the formula.

Shweta Srivastava
 
S

Shweta Srivastava77

Thanks to elobarte the formula,Stefi.

I did some mistake while copying so it was giving error earlier..
but still am not able to see the DOB highlighted.. Don't know why??

Shweta Srivastava
 
T

Tendresse

Ok guys, i tried something and it seems to be working. Let me know your
thoughts.

Under 'conditional formatting', i put the condition as follows:

Cell Value is Between TODAY( ) and TODAY( ) + 14

This will make the cell highlighted for the whole 14 day period (until the
birthday date itself), not just for one day.

Let me know if i'm missing something.

Thank you all for your input. Much appreciated.
 
Top