How to do this!

Posted on 2012-09-04
Last Modified: 2012-10-04
How to do this work
Thank you!

how to do
Question by:dungla2012
    LVL 10

    Expert Comment

    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    That was not a very useful title for a question. These titles help the experts to pick questions which they can address best. Also to access the asked questions the titles are the main identification to what could be the content in the question and also help people find questions close to their needs.
    LVL 14

    Accepted Solution

    This link will get you started on using dates to trigger conditional formatting.

    You may need to modify the instructions a bit to suit your version of Excel.
    LVL 17

    Expert Comment

    Select the who;e rows from 6 onwards and add a new conditional formatting rule with the formula

    Open in new window

    .  The add the formatting you want.
    LVL 31

    Expert Comment

    by:Rob Henson
    Slight tweak to andrewssd3's suggestion, you can use the FLOOR function:


    The conditional formatting is looking for a TRUE or FALSE result, the formula above will give a TRUE or FALSE without needing the IF statement above.

    The FLOOR function rounds a number down to the specified factor, in this instance rounding down to the previous mulitple of 7. This is because Excel stores dates as a serial number of number of days since 01/01/1900 with Saturday being the day 7 of each group (week), therefore multiples of 7 will be consecutive Saturdays.

    Your sheet above shows a little inconsistency, the day shows Sunday but 9/1/2012 (assuming 1st September) was a Saturday.

    Rob H

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now