Create Medicine Batch IDs
As an Inventory Nurse, I need to create unique and traceable Batch IDs for all incoming medicine shipments. This involves combining the 'Drug Name' with its 'Lot Number' and 'Expiry Date' to ensure accurate tracking and recall management. Manually typing these combinations is time-consuming and prone to errors.
=CONCATENATE(A2,"-",B2,"-",TEXT(C2,"YYYYMMDD")) How it works: The `CONCATENATE` function joins multiple text strings into one. In this scenario, it combines the drug name (A2), a hyphen for readability, the lot number (B2), another hyphen, and the expiry date (C2). The `TEXT` function is crucial here to format the expiry date consistently as YYYYMMDD, preventing it from appearing as a serial number. This creates a standardized, unique batch ID that is easy to read and use for inventory management, tracking, and quick identification during audits or recalls.
Data Setup
| "Drug Name" | "Lot Number" | "Expiry Date" |
|---|---|---|
| "Paracetamol" | "L12345" | "2025-12-31" |
| "Amoxicillin" | "M67890" | "2024-06-15" |
| "Ibuprofen" | "N11223" | "2026-03-20" |
Step-by-Step Guide
Assume 'Drug Name' is in Column A, 'Lot Number' in Column B, and 'Expiry Date' in Column C, starting from row 2.
Select cell D2, where you want the first Batch ID to appear.
Enter the formula: `=CONCATENATE(A2,"-",B2,"-",TEXT(C2,"YYYYMMDD"))`
Press Enter to see the generated Batch ID.
Drag the fill handle (the small square at the bottom-right of cell D2) down to apply the formula to the rest of your medicine inventory data.