I am trying to create a spreadsheet to help with medication inventory. I want
to have the cell change colors at different intervals to correspond to
different dates to give me different warning levels. For example, if a
medication is to exipre within 30 days, I would like the cell to change to
yellow and when it becomes 7 days or less I would like it to change to red. I
have not been able to figure this out. Please help. Thank you.

Assuming the expiry dates (real dates) are running in B2 down,

Select the entire sheet (with A1 active),
then apply Conditional Formatting as follows ..

Click Format > Conditional Formatting

Under Condition 1, set it as:
Formula is:
=AND($B1<>"",$B1-TODAY()<=7)
Format: Red fill

For Condition 2 (click Add), set it as:
Formula is:
=AND($B1<>"",$B1-TODAY()>7,$B1-TODAY()<30)
Format: Yellow fill

Click to OK out

The above will color (fill) entire rows based on the expiry dates in col B:
lines with expiry dates within 8 - 30 days (yellow)
lines with expiry dates within 7 days or less (red)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"emtp6738" wrote:
> I am trying to create a spreadsheet to help with medication inventory. I want
> to have the cell change colors at different intervals to correspond to
> different dates to give me different warning levels. For example, if a
> medication is to expire within 30 days, I would like the cell to change to
> yellow and when it becomes 7 days or less I would like it to change to red. I
> have not been able to figure this out. Please help. Thank you.

Here's just a quick working sample to complement:
http://cjoint.com/?jAdxHawM1r
CF_example_date conditions.xls
(with CF dialog screenshot)

Adapt to suit. The sample construct conditionally colors entire rows. If you
want to only conditionally color a single col, say the medication names in
col A, then just select col A (instead of the entire sheet), and apply the
same cond format formulas (no change).

If the CF doesn't work, probably the dates in col B are not real dates
recognized by Excel. Try selecting col B, click Data > Text to Columns. Click
Next > Next. In step 3 of the wizard, under Column data format, check "Date",
then select the format from the droplist next to it, eg: DMY, to suit. Click
Finish. This might suffice to convert it to real dates.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---