Basic (hopefully) Question. Colour format by date and row format by text.

    Hello.


    My company records some information as seen below to track services on our products.


    The first date (D) is the date we receive the part and it needs to be delivery back to the customer within 14 working day and recorded in E, the second date.


    So. I would like to do the following. From the date recorded in D to todays date.....


    Between 1-10 days have D filled as green.

    Between 11-14 days have D filled as Yellow

    Above 14 days have D filled as red.


    However if C, which now has "Received", has "Complete" written the entire row is coloured Green regardless of date.


    Can someone help? I was hoping it was simple to people more competent than me and it would be great if it could all be done in one formula?


    Thanks


    Michael


    RTM 2537 Received 01/08/2019 08/08/2019 Received from Amey Rail EST - 647   65953751139 RTM211092537 7729876878 Updated HAYS User: Carillion Robert Gray 7806 975 533      

    You can do this with conditional formatting in the cells in column D.

    Click on the date cell in column D

    Select Conditional Formatting from the toolbar ribbon

    You are going to make 4 rules on a single cell setting all your conditions

    Each time you will select "Use a formula to determine which cells to format"

    Each formula will set the conditions for that format

    Enter (TODAY()-$D$1)<11 in the formula box

    select the Format button and format the Fill Green

    Select OK

    make another rule for that cell the same way except type this in the formula bar

    AND((TODAY()-$D$1)>10,(TODAY()-$D$1)<15)

    select the Format button and Fill Yellow

    next rule

    (TODAY()-$D$1)>14

    select Format button and Fill Red

    last rule

    $C$1="Complete"

    select Format button and Fill Green


    Hit OK and it should work

    I did this all in cell D1, you will have to do it in whatever cell you start with then you can copy the formatting down the column.



    I just realized you wanted the entire row turned green for that last rule. So instead of doing that on the the cell in D, highlight the whole row and do the conditional format for the whole row and then copy that row formatting down the sheet

    Well i did have a few issues with it. For some reason when i dragged it down over the whole sheet if the first cell was older than 14 days every line was filled red regardless.


    Anyway i removed the $ signs fromt he formula and dragged it and it worked fine line by line after that.


    Also for some reason the COMPLETE function did the same but when i changed that no matter what i did it would online fill the first collunm (a) and not the entire row.


    I focused that on the D row and it overwrites the RED greater than 14 days and that is fine by me.


    Thank you for your help. Its all up and running!


    I wish you and your family good health!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login