Flag Overdue Safety Inspections
I need to check if the 'Last Inspection Date' is more than 30 days ago and mark it as 'Overdue' or 'OK'. This helps me quickly identify equipment or areas requiring immediate attention for safety compliance.
=IF(TODAY()-B2>30,"Overdue","OK") How it works: This formula uses the `IF` function to perform a logical test. `TODAY()` returns the current date. When you subtract a date (like B2) from `TODAY()`, Excel calculates the number of days between them. The condition `TODAY()-B2>30` checks if the last inspection date was more than 30 days ago. If this condition is TRUE, the formula returns "Overdue"; otherwise, it returns "OK". This dynamic check ensures your safety inspection statuses are always up-to-date.
Data Setup
| Equipment ID | Last Inspection Date |
|---|---|
| EQ001 | 2024-04-01 |
| EQ002 | 2024-04-15 |
| EQ003 | 2024-04-20 |
| EQ004 | 2024-05-01 |
| EQ005 | 2024-05-15 |
Step-by-Step Guide
Open your Excel sheet containing the 'Last Inspection Date' column (e.g., Column B).
Select the cell where you want the inspection status to appear for the first item (e.g., C2).
Enter the formula: `=IF(TODAY()-B2>30,"Overdue","OK")`
Press Enter. The cell will now display 'Overdue' or 'OK' based on the date in B2.
Drag the fill handle (the small square at the bottom-right of cell C2) down to apply the formula to all other rows in your dataset.