Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How do I set up stopwatch racing times in Excel? | Excel Worksheet Functions | |||
Excel won't allow times to be negative | Excel Discussion (Misc queries) | |||
How do I set up a week by week skill training schedule in excel? | Excel Discussion (Misc queries) | |||
Adding/Averaging Times in Excel | Excel Discussion (Misc queries) |