Inventory Help

  • Thread starter Thread starter Joliet Balimba
  • Start date Start date
J

Joliet Balimba

I am trying to create an excel spreadsheet for inventory with a limited
shelf life. I would like to have each product that is expiring within
1 month turn red, within 3 turn yellow and within 6, green. The chart
would be with 4 columns, location, sku #, description and expiration
date. Is there a way to automatically have the colors change, given a
certain date in time, say today, a month from now, etc.
 
Suppose your four columns are A:D and that row 1 is some kind of header
row. Also, I converted your 1 month, 3 month, and 6 month requirements
to 30 days, 90 days, and 180 days, respectively.

Select columns A:D. Then select Format | Conditional Formatting...
Click the Add>> button twice.

For condition 1, from the first drop down box, select 'Formula Is'. In
the next field enter =AND(ISNUMBER($D1),($D1-TODAY())<=30) Click the
Format... button, and in the resulting dialog box, click the Patterns
tab. Select a red(dish) color.

Do the same for condition 2, except that the formula should be
=AND(ISNUMBER($D1),($D1-TODAY())<=90) and select a yellowish color.

For condition 3, use 180 and select a greenish color.

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2003
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office
 
Back
Top