+ Reply to Thread
Results 1 to 2 of 2

How to shade a cell when a date falls within a certain date range

  1. #1
    Registered User
    Join Date
    02-19-2008
    Posts
    1

    How to shade a cell when a date falls within a certain date range

    I'm trying to create a spreadsheet that displays shaded cells based on whether a task falls within a date range. Also the date range is being calculated so it is a formula and not just a date value. The date range is the result of a table above where I can change a project's tasks duration in order to see where it falls within the calendar year. Example: Shade cell C2 if cell C1 (row 1 contains the static dates, like a weekly calendar) is equal or greater than the date in cell A2 (my start date that is based on a formula) and is equal or less than the date in B2 (my end date that is based on a formula). If it does not fall within the range then do not shade. I would then repeat this for the next cell D2 and E2 and across the row to the end of the year. I am using Excel 2007

    I forgot one important detail in the above: C1 is a date representing the start of the week and the next cell, C2 is the start of the next week. If I use days vs weeks your solution works well but I really want to display the week vs each day to reduce the number of columns needed. Is there a way to alter the formula so that it says shade C2 if the start (A2) and end (B2) dates fall within the date range of C1 through D1? To simplify I'm really just trying to shade all the weeks a task is being performed VS the days.
    Attached Images Attached Images
    Last edited by coedog123; 02-19-2008 at 08:47 PM.

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Hi Coedog,

    For Excel 2003 and earlier:
    1. Select cells C2:xx2 (where xx = your last used column in row 2)
    2. Click Format -> Conditional Formatting
    3. Under 'Condition 1', select 'Formula Is' from the drop-down list
    4. Next to that put the formula: =AND(C1>=$A$2,C1<=$B$2)
    5. Click the 'Format' button and choose a shading/font/etc.
    6. Click OK twice to return to your sheet.

    That should do it.

    UPDATE, I see you're using Excel 2007. I'll post those instructions momentarily.

    For Excel 2007:
    1. Select cells C2:xx2 (where xx = your last used column in row 2)
    2. On the Home ribbon, click 'Conditional Formatting' -> New Rule...
    3. Beneath 'Select a rule type', choose 'Use a formula to determine which cells to format'
    4. Beneath 'Format values where this value is true', enter: =AND(C1>=$A$2,C1<=$B$2)
    5. Click the 'Format' button and set your formats
    6. Click OK twice to return to your sheet.
    Last edited by Paul; 02-19-2008 at 11:42 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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