Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing cell colors to correspond to different dates
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing cell colors to correspond to different dates
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing cell colors to correspond to different dates
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Record changing cell data into a column or range | Excel Worksheet Functions | |||
funtions for colors for fonts or cell backgrounds? | Excel Worksheet Functions | |||
Automatically Changing Cell Reference's when Pasting in Excel | Excel Discussion (Misc queries) | |||
Changing cell colour depending on another cells value... | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions |