Creating a list of courses that will soon expire.

[freesie101]

Hi there,

I'm just looking for help with some data keeping. I have a list of employees that require certain courses to be up to date and would like a function etc that will generate a list of courses that will expire within a month and the employee it corresponds to.

Thanks.
Mark

 

[Jerry Sullivan]

Welcome Mark,

What fields of infomation do you have in your current data?
Specifically, do you have a column that lists when each course will expire, or do you have separate columns that list when the last class was taken and the frequency they need to be retaken?

Do you really need to generate a new list on a separate sheet, or would it be sufficient to have a macro that filtered your current list and only showed the names courses due to expire within 30 days or less? The filter approach is sometimes more desirable because then you can keep one set of data instead of spawning off partial copies.

 

[freesie101]

Hi Jerry,

Thank you very much for your reply. My data consists of a left hand colomn with inspectors names, next to which is the expiry date in the columns of the corresponding courses.

Also, I believe a filter would be sufficient. Whatever may be easiest to manipulate on a frequent basis.

Here is a link to my data if required: http://www.mediafire.com/?1djodpn3tq3fr32

Thank you
Mark

 

[Jerry Sullivan]

Hi Mark,

There are a few Sheets in your example. Can you explain which columns on which sheet you are wanting to filter for 30 days or less?

Also, how have you hidden/deleted rows on Sheet "POLE INSP & ASSISTANTS"? IE Row 6?

 

[freesie101]

Hey Jerry,

The courses that expire are as follows:
First Aid
High Risk
PTR (Pole top rescue)
Metrel
EDD (Emergency Descent)
Licence (Motor vehicle licence)
Substation Entry
Confined space
RIC (Recipient in Charge)
Traffic Management

I think my main concern is the DISTR and POLE INSP & ASSISTANTS sheets as they are hard to keep track of.

To answer your question about the hidden rows... I'm actually not quite sure...

 

[Jerry Sullivan]

On your original post, I assumed that each row would be a separate course. You have it set up so that each person has their own row and the columns have the courses. That has the benefit of being more concise, but it also means that if we are going to show any row that has at least one course expiring soon, we might not filter out very many rows.

It looks like you were experimenting with conditional formatting. Maybe the best solution would be to use conditional formatting as follows:
Red = Expiry date is past
Yellow = Expiry date in next 30 days
Green (or no color) = Expiry 31 days or more in future.

Then as a second step, we filter so that any person who has at least one Red or Yellow is displayed; and all Green is hidden.

Would that work for you?

 

[freesie101]

Yeh, That sounds awesome.

Thanks very much.

 

[Jerry Sullivan]

Some of this could be done with macros, but it might be best to keep this simple.


Here are some steps to set it up:

1. Add a Column "Earliest Expiry" to the left of your Expiry dates.
2. Use a =MIN(...) formula as in the example below to get the earliest date in each row.

3. Excel Workbook
   	A	B	C	D	E
   2	NAME	DESIGNATION	EARLIEST	BUNDLED POLE	OPERATIONAL
   3			EXPIRY	INSPECTION	SAFETY INDUC.
   4	Name 1	Pole Inspector	11/10/2010	6/21/2012	11/10/2010
   5	Name 2	Pole Inspector	3/2/2011	3/2/2011	8/4/2011
   POLE INSP & ASSISTANTS

   Excel 2007

   Cell Formulas
   Range	Formula
   C4	=MIN(D4:L4)
   C5	=MIN(D5:L5)

4. In the Cell that is the first row of data in the new column, add 2 conditional formatting rules in this order:
   1. Rule #1
      Rule: Cell Value< TODAY()
      Format: Red
      Applies to: Select the range of all your dates including the "Earliest Expiry" dates
   2. Rule #2
      Rule: Cell Value between TODAY() and TODAY()+30
      Format: Yellow
      Applies to: Select the range of all your dates including the "Earliest Expiry" dates
5. Apply Autofilters, to your entire data range.
6. Filter the "Earliest Expiry" column:
   1. Date Filters > Before... > pick Today

Sorry if I assumed you know too much or too little in this explanation. Just ask if I can clarify any part.

 

[Jerry Sullivan]

Mark, About those Hidden rows.... it looks like they were made hidden using Advanced Filter.

To avoid having problems with the steps above or missing some hidden data, you should clear that filter first.

If you have xl2007, Click on a cell that has a Blue Row number (which indicates it is part of a filtered range). Then in the Ribbon go to the Data tab > Sort & Filter group > Clear. Your rows should all unhide.