training cert expiration times need to be flagged in excel
 

I have been given the task of creating a spreadsheet of employees which will
flag 3-months before a certification is to expire,i.e., CPR,First
Aid,Firearms, etc.
Do I need to milestone the event or is there something easier. I am a
beginner in using this program, but I am willing to take on the task.

training cert expiration times need to be flagged in excel
 

You may want to simply use a conditional format.
The following example is very close to what you would need to do:
http://www.officearticles.com/excel/...ft_ excel.htm
Your formula would be something like =TODAY()-B2<90
(The "90" being the number of days away, and B2 containing the expiration
date.)

--
~Anne Troy
www.OfficeArticles.com


"Wkdwzdm" wrote:

I have been given the task of creating a spreadsheet of employees which will
flag 3-months before a certification is to expire,i.e., CPR,First
Aid,Firearms, etc.
Do I need to milestone the event or is there something easier. I am a
beginner in using this program, but I am willing to take on the task.

training cert expiration times need to be flagged in excel
 

Being in EMS, I have set this type of spreadsheet up before. I used a 3
month, 1 month, and expired condition (all three condition formats options).

If the expiration date for a CPR card is in B2:

Conditional Format 1): =AND(B2<"",TODAY()=B2-90,TODAY()<B2-30)
Conditional Format 2): =AND(B2<"",TODAY()=B2-30.TODAY()<B2)
Conditional Format 3): =AND(B2<"",TODAY()=B2)

Using the "Formula Is" option in conditional formatting, use these formulas
as alerts. The first checks for with 90 days but greater than 30 days out.
Number 2 checks for expiration within the next 30 days, and the last is
expired. Respectfully I used the following color formatting
(font/background): 1) black/yellow, 2) black/orange, and 3) white/red.

You will note also in the formula, that I am also checking to see if there
is a value in cel B2.

Hope this helps you out.
Les

"Wkdwzdm" wrote:

I have been given the task of creating a spreadsheet of employees which will
flag 3-months before a certification is to expire,i.e., CPR,First
Aid,Firearms, etc.
Do I need to milestone the event or is there something easier. I am a
beginner in using this program, but I am willing to take on the task.

training cert expiration times need to be flagged in excel
 

This was okay,but it only flagged one event. I figure the syntax was wrong
somewhere. Thank you:-)

"Anne Troy" wrote:

You may want to simply use a conditional format.
The following example is very close to what you would need to do:
http://www.officearticles.com/excel/...ft_ excel.htm
Your formula would be something like =TODAY()-B2<90
(The "90" being the number of days away, and B2 containing the expiration
date.)

--
~Anne Troy
www.OfficeArticles.com


"Wkdwzdm" wrote:

I have been given the task of creating a spreadsheet of employees which will
flag 3-months before a certification is to expire,i.e., CPR,First
Aid,Firearms, etc.
Do I need to milestone the event or is there something easier. I am a
beginner in using this program, but I am willing to take on the task.

training cert expiration times need to be flagged in excel
 

This worked perfectly!!!! THANKS!!! You are great :-)

"WLMPilot" wrote:

Being in EMS, I have set this type of spreadsheet up before. I used a 3
month, 1 month, and expired condition (all three condition formats options).

If the expiration date for a CPR card is in B2:

Conditional Format 1): =AND(B2<"",TODAY()=B2-90,TODAY()<B2-30)
Conditional Format 2): =AND(B2<"",TODAY()=B2-30.TODAY()<B2)
Conditional Format 3): =AND(B2<"",TODAY()=B2)

Using the "Formula Is" option in conditional formatting, use these formulas
as alerts. The first checks for with 90 days but greater than 30 days out.
Number 2 checks for expiration within the next 30 days, and the last is
expired. Respectfully I used the following color formatting
(font/background): 1) black/yellow, 2) black/orange, and 3) white/red.

You will note also in the formula, that I am also checking to see if there
is a value in cel B2.

Hope this helps you out.
Les

"Wkdwzdm" wrote:

I have been given the task of creating a spreadsheet of employees which will
flag 3-months before a certification is to expire,i.e., CPR,First
Aid,Firearms, etc.
Do I need to milestone the event or is there something easier. I am a
beginner in using this program, but I am willing to take on the task.