+ Reply to Thread
Results 1 to 5 of 5

Use conditional formatting to track expiry dates in multiple fields

  1. #1
    Registered User
    Join Date
    08-13-2013
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    3

    Use conditional formatting to track expiry dates in multiple fields

    Hi,

    Am struggling to format this spreadsheet correctly! Am an administrator for a transport company and need to keep track of 100+ subcontractor insurances. There are 8 different kind of insurances/fields (some insurances apply to businesses operating from certain states, so some cells are blank). The expiry dates all vary, and every time i use the formulas i've googled it fills blank cells and overrides the formula i entered previously!

    Need to come up with a way to format cells so:
    Green = Insured from 3 weeks from now onwards;
    Yellow = Insurance set to expire within 3 weeks (to send reminders to subbie to resubmit)
    Red for all expired from today prior.

    Load allocators need an easy and simple way to view so they can know which business can load for us and which can't.

    Edit: As an industry that is 20 years behind the rest of us and highly resistant to change, using Outlook Calendar reminders or something similar will not be as effective. If i can send a copy of this at the start of each month they can use as reference, it would be much easier! Am currently using conditional formatting for 'values between' but this needs constant maintenance - and this spreadsheet needs to be up to date so all employees can look at any time, and assign a loadAttachment 257682.
    Due to privacy restrictions, I have uploaded a modified version, omitting sensitive information e.g Business names, vehicle registrations, ABNs etc



    Any help would be appreciated!!
    Last edited by shamilton; 08-14-2013 at 05:49 AM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Conditional Formatting

    To attach a Workbook
    (please do not post pictures of worksheets)
    • Click Advanced (next to quick post),
    • Scroll down until you see "Manage Attachments",
    • Click that then select "add files" (top right corner).
    • Click "Select Files" find your file, click "open" click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Click "Done" at bottom right to close the Attachment Manager.
    • Click "Submit Reply"
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    08-13-2013
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Conditional Formatting

    Workbook uploaded

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Use conditional formatting to track expiry dates in multiple fields

    M2: =NOW()

    N2: =M2-21

    O2:=M2+21

    Red: =AND(ROW()>2,B1<>"",B1<$N$2)

    Yellow:=AND(ROW()>2,B1>=$N$2,B1<=$O$2)

    Green:=AND(ROW()>2,B1>=$N$2,B1<=$O$2)

  5. #5
    Registered User
    Join Date
    08-13-2013
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Use conditional formatting to track expiry dates in multiple fields

    Thanks protonLeah!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Delete Conditional Formatting conditions but keep cell formatting - Excel 2010
    By tetreama in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-09-2012, 08:28 PM
  2. Replies: 3
    Last Post: 05-15-2012, 04:13 PM
  3. Replies: 1
    Last Post: 03-05-2012, 06:20 PM
  4. Macro for formatting fonts and cell colours - not Conditional Formatting
    By Kayaness in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-07-2011, 03:46 AM
  5. Replies: 2
    Last Post: 11-02-2007, 12:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1