+ Reply to Thread
Results 1 to 4 of 4

Return "green", "yellow" or "red" from date/age and priority ranking

  1. #1
    Registered User
    Join Date
    04-25-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    3

    Return "green", "yellow" or "red" from date/age and priority ranking

    I'd like to employ some conditional formatting based on the created date/age and priority ranking (0, 1, 2, 3) of a line item. Where a line item = work assignment. The goal being a health assessment visualization on the age and priority of work assignments.

    Something like:

    If WORK_ASSIGNMENT_CREATION_DATE is between 7 and 14 days ago and PRIORITY_RANKING is 0, return "green". If WORK_ASSIGNMENT_CREATION_DATE is between 15 and 30 days ago and PRIORITY_RANKING is 0, return "yellow". If WORK_ASSIGNMENT_CREATION_DATE is between 31 and 45 days ago and PRIORITY_RANKING is 0, return "red".


    Where WORK_ASSIGNMENT_CREATION_DATE=Date in A1
    and
    PRIORITY_RANKING=0, 1, 2 or 3 in B1

    Many thanks.
    Last edited by Cantaloop; 01-03-2013 at 10:08 PM. Reason: Solved

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Return "green", "yellow" or "red" from date/age and priority ranking

    the best way to work CF's like this out is to create them in the workbook itself to begin with. it saves a bit of effort in opening CF, adjusting/typing the formula, exiting etc. once you have the formula perfected, copy/paste into the CF window

    create the formula to give a TRUE or FALSE return. you are looking for a TRUE. so, your 1st argument would be something like...
    =and(WCD<=today()-7,WCD>today()-14,PR=0) not tested

    try that syntax, using the correct ranges and see how you get on. if you still have a proble, perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-25-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Return "green", "yellow" or "red" from date/age and priority ranking

    Got there with your help, and some "helper" columns.


    This (and friends in subsequent columns for different date ranges):
    =AND(B2<=TODAY(),B2>TODAY()-7,D2=0)

    Did the trick when coupled with:
    =IF(E2=TRUE, "Green", IF(F2=TRUE, "Yellow", IF(G2=TRUE, "Red")))

    Many thanks.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Return "green", "yellow" or "red" from date/age and priority ranking

    happy to help and thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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