Calculate Product Expiration Status
As an Inventory Manager, I need to determine if a product is expired by comparing its 'Use By' date (which is stored in separate year, month, and day columns) to today's date. This helps in managing stock rotation and preventing the sale of expired goods.
=IF(DATE(B2, C2, D2) < TODAY(), "Expired", "Not Expired") How it works: This formula works by first constructing the full 'Use By' date using the `DATE` function, combining the year from B2, month from C2, and day from D2. It then compares this constructed date to the current date, retrieved by the `TODAY()` function. If the 'Use By' date is earlier than today's date, the `IF` function returns 'Expired'; otherwise, it returns 'Not Expired'. This provides a dynamic and accurate status for each product.
Data Setup
| Product Name | Use By Year | Use By Month | Use By Day |
|---|---|---|---|
| Milk | 2023 | 12 | 31 |
| Yogurt | 2024 | 1 | 15 |
| Cheese | 2024 | 3 | 15 |
| Butter | 2023 | 11 | 20 |
| Juice | 2024 | 2 | 28 |
Step-by-Step Guide
Assume your product data is in a sheet where 'Use By Year' is in Column B, 'Use By Month' in Column C, and 'Use By Day' in Column D, starting from row 2.
In a new column (e.g., E2), enter the formula: `=IF(DATE(B2, C2, D2) < TODAY(), "Expired", "Not Expired")`.
Press Enter to see the result for the first product.
Drag the fill handle (the small square at the bottom-right corner of cell E2) down to apply the formula to all other products in your inventory list.
Explore More
Identifying Supplier Contact Persons
As an Inventory Manager, I frequently need to contact suppliers regarding stock levels, new orders, or delivery issues. I have a list of suppliers with their unique IDs, but often need to quickly find the specific contact person for a given supplier (e.g., from cell E2). Manually searching through a large supplier list is inefficient and can delay critical communications.
Extracting All Details for a Specific Item
An Inventory Manager needs to quickly retrieve all associated details (Description, Quantity, Location) for a specific Item ID. Manually copying and pasting multiple cells for each item is tedious and error-prone, especially when managing a large catalog.
Find Stock Level by SKU
I need to look up the current stock quantity for a specific product SKU from a large inventory table.
Categorize Products by SKU Prefix
I need to extract the first 2 letters of an SKU (e.g., 'FU' from 'FU-101') to determine the product category (Furniture).