Count Expiring Batches
I need to count how many batches have an 'Expiry Date' within the next 30 days.
=COUNTIFS(C2:C7, ">="&TODAY(), C2:C7, "<="&TODAY()+30) How it works: This COUNTIFS formula uses two criteria to define a date range. The `TODAY()` function dynamically retrieves the current date. The first criterion, `">="&TODAY()`, counts items with an expiry date on or after today. The second criterion, `"<="&TODAY()+30`, counts items with an expiry date on or before 30 days from today. By combining these, COUNTIFS accurately tallies only those batches whose expiry dates fall within the specified 30-day window, starting from today.
Data Setup
| Batch ID | Product | Expiry Date | Quantity |
|---|---|---|---|
| B001 | Milk | 2023-10-25 | 100 |
| B002 | Yogurt | 2023-11-15 | 50 |
| B003 | Cheese | 2023-12-01 | 75 |
| B004 | Butter | 2023-10-01 | 200 |
| B005 | Cream | 2023-11-05 | 120 |
| B006 | Juice | 2023-12-20 | 90 |
Step-by-Step Guide
Identify the column containing your 'Expiry Date' data (e.g., Column C).
Determine the range of cells for your expiry dates (e.g., C2:C7).
The first criterion checks for dates greater than or equal to today's date: `">="&TODAY()`.
The second criterion checks for dates less than or equal to today's date plus 30 days: `"<="&TODAY()+30`.
Combine these into the COUNTIFS function: `=COUNTIFS(C2:C7, ">="&TODAY(), C2:C7, "<="&TODAY()+30)`.
Enter this formula into any empty cell to get the count of batches expiring within the next 30 days.
Explore More
Tracking Products Below Reorder Threshold
An Inventory Specialist needs to quickly identify how many different products currently have a stock level below 10 units. This helps prioritize reorders to prevent stockouts and maintain optimal inventory levels.
Generating Automated Inventory Reorder Alerts
An Inventory Specialist needs to monitor stock levels for various products and quickly identify which items are below their reorder point (e.g., 50 units) to prevent stockouts. Manually reviewing hundreds of items is inefficient.
Generating Unique Product SKUs
As an Inventory Specialist, I need to create unique Stock Keeping Units (SKUs) for new products. Each SKU must combine the product category, color, and size to ensure accurate tracking. Manually constructing these SKUs for hundreds of items is time-consuming and prone to transcription errors. I need an automated way to generate these codes consistently.
Pinpointing Product Location in Inventory
An inventory specialist needs to quickly find the exact row number of a specific product in a large inventory spreadsheet to update its stock level or details. Manually scanning thousands of rows is time-consuming and prone to errors, especially when dealing with dynamic data.