+ Reply to Thread
Results 1 to 2 of 2

Logical If returns with multiple trues from a two cells by date comparison

  1. #1
    Registered User
    Join Date
    12-04-2012
    Location
    Johnstown, CO
    MS-Off Ver
    Excel 2007
    Posts
    1

    Question Logical If returns with multiple trues from a two cells by date comparison

    I have an issue. If this is not possible or there is another way to do this I would love to know. Any help is appreciated.

    I have a worksheet with a column for dates and another column for due dates. ex. 10/20/2012 in last done column. Maintenance required in another column. I have a formula comparing the fixed due dates against today's date and the last done date to warn that maintenance is required by returning a logical 1 or 0 in a separate column and conditionally formatting it to turn red. This is the formula I am using. =IF((TODAY()-L19)>=120,1,0)
    Where 120 is a set amount of days before a required maintenance and L is the last performed maintenance. What I would like is for the column with conditional formatting to have multiple colors to warn of the upcoming maintenance at different intervals based on color before the actual due date all in a single column. Like if maintenance is one month or greater away as green, 2 weeks as orange and due and or overdue as red. I currently have it as logical returns 1 and cells are red, 0 and cells are white. Now the only way I could figure this would be remotely possible is to have nested IF statements, but IF is still based on the first logical true. What I was considering was can you have a formula that says if cell L# displays a date more than 120 days out than it returns say a 1 for true and a 0 for false and on top of that says at 113 days out 1 week before the due date return 2 if true and 0 if false and on and on for the due dates at intervals before they are due at say 1 week, 2 weeks, a month. Then the column with the true returns could be formatted by color based on the number of the true return. Right now it can only show that the maintenance is due since it only returns a 1 or a 0 that turns the column red.

    Last Done - 10/20/2012
    Frequency 120 days
    Maintenance required 1=red cell
    0=white cell

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Logical If returns with multiple trues from a two cells by date comparison

    I think you are looking for conditional formatting, each separation as a rule...you'd have to enter a new rule for each time frame and color you want, but it seems you have the basic logic and formulas for each rule !

    The conditional formatting in Excel 2007 is on the home tab, about the middle of the ribbon, and for what what you are looking at, you probabaly want the new rule->formula options

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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