+ Reply to Thread
Results 1 to 4 of 4

Conditional formatting based on a Week commencing date

  1. #1
    Registered User
    Join Date
    06-18-2021
    Location
    Bournemouth, UK
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    7

    Conditional formatting based on a Week commencing date

    Hello all

    Continuing on with my conditional formatting I have another question... this tie I have attached an example sheet.

    I have data entered on a weekly basis. It's always in arrears so on 23 August I will be entering data for week beginning 16 August. I have some cells reading from another sheet but for the example I have created a small grid with some numbers in it. As the example shows, what I would like is the greyed numbers in line with 16 Aug to go black when we hit 23 August. On 30 August I would like the numbers to go black on the line for 23 August.

    Hope that makes sense. I know you guys won't fail me.

    I won't be back on until Monday now so hope you have a great weekend.

    regards
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,234

    Re: Conditional formatting based on a Week commencing date

    Try
    =AND($B2<=TODAY(), B2<>"")

    for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
    Conditional Formatting

    Highlight applicable range >>
    C2:E100, however many rows you need

    Home Tab >> Styles >> Conditional Formatting
    New Rule >> Use a formula to determine which cells to format
    Edit the Rule Description: Format values where this formula is true:
    =AND($B2<=TODAY(), B2<>"")

    Format [Number, Font, Border, Fill]
    choose the format you would like to apply when the condition is true
    OK >> OK
    Attached Files Attached Files
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,282

    Re: Conditional formatting based on a Week commencing date

    Select C2:E5. CF formulas
    For black

    =AND($B2<>"",$B2<(TODAY()-7))

    for Grey

    =AND($B2<>"",$B2>=TODAY())
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    06-18-2021
    Location
    Bournemouth, UK
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    7

    Re: Conditional formatting based on a Week commencing date

    Thank you both. I have added the formats and am going to wait until Monday to confirm it does what I need. Appreciate your help and sorry for the delay in gettin g back on here.

    Regards

+ 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. If formula for week commencing date
    By pauldaddyadams in forum Excel General
    Replies: 1
    Last Post: 01-13-2020, 06:19 AM
  2. Returning a week commencing date
    By sdts in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-15-2019, 07:08 PM
  3. [SOLVED] Find where a specific week commencing date falls within a rolling10 week pattern
    By C J W in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-14-2017, 04:39 AM
  4. [SOLVED] Work out week commencing date
    By Bunny Screen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-20-2014, 01:12 PM
  5. Generating week commencing date for 52weeks.
    By sritantry in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-17-2012, 01:36 AM
  6. Automatic week commencing date
    By loz786 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-15-2011, 03:57 PM
  7. Week commencing Date
    By amarpabari in forum Excel General
    Replies: 1
    Last Post: 07-06-2011, 04:53 AM

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