Changing cell colors to correspond to different dates

[Guest]

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.

 

[Guest]

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)

 

[Guest]

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.

---