Spreadsheet for Certificates

[MatthewRush]

Hi,

I'm quite new to Excel.

Basically i need to create a table for Insurance Certificates running out of date..Headings - Name of Colleague - date of insurance start - Insurance end date ..What i need is something to tell me with a highlighted cell when it is coming up to end date say 1 month before in Amber colour - When it is passed due date in red...Hope this makes sense...Any help massively appreciated

Thank you in advance

 

[L. Howard]

Hi, and welcome to the board.

You will want to take a look at Conditional Formatting.

On your worksheet, use some test formulas to return TRUE for each of the conditions you want a cell color alert. Those formulas will be the ones to use in Conditional Formatting, which is on the HOME tab on the tool bar.

Home > Conditional Formatting > Highlight Cell Rules > More Rules > Use a formula to determine which cells to format > enter your formulas in the window labeled > Format values where this formula is true: > Format > Fill > select you color of choice > OK out.

I should add, delete the formulas on the worksheet, those were just to school you on which one to use in the conditional formatting wizard.

Howard

 

[skypolice]

I used to track my unit members different trainingexpiration dates with conditional formatting.<o></o>
For Current dates:<o></o>
Format only cells that contain:<o></o>
Cell value greater than =TODAY()+90<o></o>
Format Font BoldBlue Fill Green<o></o>
For Expired Dates:<o></o>
Format only cells that contain:<o></o>
Cell value less than =TODAY()<o></o>
Format Font Bold Yellow Fill Red<o></o>
For Dates Expiring in a month:<o></o>
Format only cells that contain:<o></o>
Cell value between =TODAY()+1 and =TODAY()+30 <o></o>
Format Font Bold RED Fill Yellow<o></o>
For Dates Expiring within two months:<o></o>
Format only cells that contain:<o></o>
Cell value between =TODAY()+31 and =TODAY()+60 <o></o>
Format Font Bold White FillBlue<o></o>
Empty cells:<o></o>
Format only cells thatcontain:<o></o>
Cell Value =””<o></o>
Format Font Gray Fill Gray<o></o>