Managing Expiry Dates

rehabguru

New Member
Joined
Sep 8, 2014
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Dear Excel Gurus

Thank you for your collective wisdom.

I have a large table of team members from several sites that undertake mandatory training. Currently it lists names in descending rows against their courses across the top. The values against the names and courses are dates that they took the training. Calculated fields to the right of course work out when the dates expire. I have used the expertise given in this forum to conditionally format them according to how close the expiry date is approaching, using red, amber green for expired, within 30 days and greater than 30 days. Some courses last for 6 months, others for example may expire after 2 years.

What I trying to do is to add a slicer to filter out the dates according to the proximity of the expiry date so I can print out just the reds for example. In addition I could filter it by team, then by expiry. My thoughts are that it would be a conditionally formatted pivot table but I cannot seem to get the course title across the columns, only the dates of the courses taken. I have tried to demonstrate what I have in the table below.

NameTeamFirst AidExpiry date - calc fieldManual HandlingExpiry - calc fieldSecurity trainingExpiry date - calc field
BloggsWarehouse12/3/2011/9/2112/3/2211/3/2112/3/2011/3/22
JonesShop18/4/2017/10/2025/9/2024/9/2106/2/2005/2/22
SmithManagement12/12/1911/6/2015/1/2014/1/2118/2/2017/2/21

How my pivot chart is looking is like this:

SurnameJanFebMarAprJunJulAugSepOct
Bloggs11/3/21
Jones17/10/20
Smith11/6/20

It is picking out the minimum date using the field value MIN in the pivot table. Across many personnel it would be ideal to filter by course to find out how many have expired etc, then by team if possible


In my table there are 100 rows of names for 10 different teams across 6 location all with very differing expiry dates. In essence I am trying to add a slicer or two on my spreadsheet that will filter rather than rely on the filter function of excel as each site will be responsible for data entry, with varying skillsets. Therefore, to make it simple to use I would like a slicer for team, one for course selection and one for expiry date please. So far I have filtered the pivot table by the expiry date but I have not fathomed how to link the varying course data in the table. My thoughts are that it should be part of a data model but currently I am stuck so I would greatly appreciate your thoughts and direction. I hope this makes sense to you.

Warmest thanks
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Nvablejrg

New Member
Joined
Sep 10, 2008
Messages
27
rehabguru,

Good day. I recently had to solve the same or similar issue, if I am understanding you correctly.

I do not see a way to attached a file to allow you to see what I have. I honestly cannot explain it. Sorry.

Hopefully you can see the formulas. You will have to conditional format for the colors. It would be nice to be able to upload a file.

Nvablejrg

Forcast.PNG

Conditional Formatting.PNG
Expiration Date Formulas.PNG
 

rehabguru

New Member
Joined
Sep 8, 2014
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi Nvablejrg

Apologies for my tardy reply. Thank you very much - this went a long way to solving my problem. I used your formulae but tweaked the way I was recording it in the tables so this method sat much better. Thanks again for your expertise.
 

Nvablejrg

New Member
Joined
Sep 10, 2008
Messages
27
rehabguru,

You are welcome. I am glad I can share what knowledge I have gathered from the experts on these forums.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,441
Members
410,684
Latest member
LakTik
Top