+ Reply to Thread
Results 1 to 4 of 4

RAG sheet time tracker issue - need help!

  1. #1
    Registered User
    Join Date
    11-01-2018
    Location
    London, England
    MS-Off Ver
    16
    Posts
    14

    RAG sheet time tracker issue - need help!

    Hi all

    I'm not sure whether this is a conditional formatting or VBA issue, so I apologise if I have submitted this on the wrong forum.

    I am attempting to create a RAG sheet which will track the creation of manuals.

    I work for a company that has to create 2 x manuals for every job we do to provide information to our clients before and after the the job.

    The RAG sheet is being put in place to monitor the time it takes for each person to create and supply each manual for each job.

    I currently have a sheet with the current date (using TODAY()) in column A1, the Start dates in column F, Due dates column G.

    The sheet should show the job as green if TODAY() is 1 day or more before the due date

    The sheet should show the job as amber if TODAY() is the same as the due date

    The sheet should show the job as red if TODAY() is after the due date

    So for example, if a Due Date is 03/11/18 and the Start Date is 15/10/418 the job should be green until 02/11/18, amber on 03/11/18, and red on 04/11/18 onward

    I also need to be able to find a way to 'close' a job on the sheet, so once a manual has been created it won't just keep showing as red. I want to be able to input this myself.

    Currently I've been trying conditional formatting but with no luck, as I can't even seem to get the sheet to turn red/green/blue with the simplest formula, so I imagine I am writing it wrong, but excel is not showing that there are any errors and I can't find any information for what I want specifically online

    Green: =IF(Today()=G2=1, Green)
    Amber: =IF(Today()=G2, Amber)
    Red: =IF(Today()>G2, Red)

    Thank you for your help in advance
    Last edited by ConstructT; 11-02-2018 at 09:06 AM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,408

    Re: RAG sheet time tracker issue - need help!

    Select A2:Axx Press Alt H L R for CF rules >
    1. New rule > Use a formula > …Formula is true: >

    =today()<$G1
    > Format > Fill > Green > OK

    2. New rule > Use a formula > …Formula is true: >

    =today()=$G2
    > Format > Fill > Amber> OK>

    3. New rule > Use a formula > …Formula is true: >
    =today()>$G2
    > Format > Fill > Red> OK>

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,910

    Re: RAG sheet time tracker issue - need help!

    Try selecting all cells which shall become red (if their value in column G has passed) like: C2:H100 (or whichever row data ends) and use for formating red rule based on formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    it's important to note that there is a row number of your first selected row, and column name is made absolute (by using dollar sign).
    for amber it would be (as you can expect)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and let's say that closed manual are marked with word "Closed" in column H then for green your formatting rule could be based on formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  4. #4
    Registered User
    Join Date
    11-01-2018
    Location
    London, England
    MS-Off Ver
    16
    Posts
    14

    Re: RAG sheet time tracker issue - need help!

    Your help solved my problems straight away. Amazing, thanks guys!
    Last edited by ConstructT; 11-02-2018 at 09:09 AM.

+ 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. Spend Tracker - Array cross reference issue
    By Richard Connell in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-26-2018, 07:21 AM
  2. [SOLVED] Trying to create a Dashboard for Time sheet Tracker
    By jodo2 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-31-2017, 04:20 PM
  3. Excel issue - revenue tracker
    By Dooberry in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-10-2016, 06:31 AM
  4. Help build a Leave tracker and Effort Time Estimation Tracker
    By cherias in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2015, 02:10 PM
  5. System idle time tracker / Break Tracker
    By reetika05 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-10-2014, 07:26 AM
  6. [SOLVED] Time sheet tracker: sum instance of text formula issue
    By smls in forum Excel General
    Replies: 5
    Last Post: 08-28-2012, 02:47 PM
  7. time tracker-a running tracker date wise
    By arnab0711 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-18-2008, 03:08 PM

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