Managing Expiry Dates

rehabguru

New Member
Joined
Sep 8, 2014
Messages
10
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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.
 
Upvote 0
rehabguru,

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

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top