How to Find Courses Expiring Within 30, 60, or 90 Days in Excel
Question details
The user wants to track student course completions and identify which certifications are expiring within the next 30, 60, or 90 days, assuming each course is valid for one year.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing employee or student training matrices where course certifications expire one year after the completion date, requiring proactive tracking of upcoming expirations.
- Observed behavior
- Needs a reliable formula or method to automatically calculate expiration dates from historical completion records and filter them by approaching 30, 60, or 90-day deadlines.
Ensure your training records are organized in a clean, tabular format with separate columns for the student's name, the course name, and the completion date. It is highly recommended to convert your data range into an official Excel Table so that formulas automatically apply to new entries.
Calculate Expiration Dates using EDATE and TODAY Functions
Use Excel's date functions to automatically add one year to the completion date, calculate the remaining days, and filter for upcoming expirations.
The EDATE function is the most reliable way to calculate exactly one year from a given date, as it automatically accounts for leap years and different month lengths.
Insert a new column named 'Expiration Date' next to your 'Completion Date' column. In the first empty cell of this new column, enter the formula =EDATE(C2, 12) (assuming C2 contains the completion date) to add exactly 12 months.
Add another column named 'Days Until Expiration'. Enter the formula =D2 - TODAY() (assuming D2 is your newly calculated Expiration Date) and press Enter. Ensure this column is formatted as a General number, not a date.
Select your table headers, navigate to the 'Data' tab on the ribbon, and click 'Filter'.
Click the filter dropdown arrow on the 'Days Until Expiration' column. Choose 'Number Filters' > 'Between'. Set the criteria to be greater than or equal to 0 and less than or equal to 30 (or 60, 90). Click 'OK' to display the students whose courses are expiring in that timeframe.
Highlight Expiring Courses using Conditional Formatting
Visually flag courses that are expiring soon by applying color-coded rules directly to the date column, avoiding the need for extra filter columns.
Calculate and Track Expiration Dates in WPS Spreadsheet
WPS Spreadsheet offers comprehensive formula support, including EDATE, TODAY, and advanced array functions, making it incredibly simple to manage training matrices and certification expirations. It provides a familiar interface to efficiently track deadlines and analyze data.
- 1. Open your training records: Launch WPS Spreadsheet and open your existing training database.
- 2. Calculate expiration dates: Insert a new column for 'Expiration Date' and type =EDATE(C2, 12) to calculate the 1-year validity period.
- 3. Filter upcoming dates: Use the AutoFilter tool under the Data tab to filter records falling within the next 30, 60, or 90 days.
- 4. Apply color-coding: Go to the Home tab and apply Conditional Formatting to color-code rows that are expiring soon.
Frequently Asked Questions
How do I calculate an expiration date if the course is valid for 3 years instead of 1 year?
You can modify the EDATE function to add 36 months instead of 12. Use the formula =EDATE(C2, 36) where C2 is the cell containing your original completion date.
Why is my EDATE formula returning a strange number like 45120 instead of a date?
Spreadsheet software stores dates as sequential serial numbers. If you see a number like 45120, select the affected cells, go to the Home tab, and change the Number Format dropdown from 'General' to 'Short Date'.
Can I use the IF function to label courses as 'Expired' or 'Valid' automatically?
Yes, you can combine the IF function with TODAY(). Use the formula =IF(D2<TODAY(), "Expired", "Valid") where D2 is your calculated expiration date. You can also nest IF functions to add a third status like 'Expiring Soon'.
How can I find the most recent completion date if a student took the course multiple times?
You can use a PivotTable. Place the Student Name and Course in the Rows area, and the Completion Date in the Values area. Right-click the date values, select 'Value Field Settings', and change the summary calculation from 'Count' to 'Max' to display only the latest date.
