+ Reply to Thread
Results 1 to 4 of 4

Help highlighting cells in a date range

  1. #1
    Registered User
    Join Date
    01-12-2019
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    2

    Help highlighting cells in a date range

    Hi all,

    I'm currently making a data analysis spreadsheet for my work and I'm trying to conditionally format cells based on what time of year it is.

    So for example, if a student has a grade ELG in cell A2 and today's date is between Sept 1st and Oct 20th (year isn't important) then the cell highlights bright green, but if the same student has the same grade between Jan 1st and Feb 19th then the cell highlights light green.

    This is how my table looks currently:

    Attachment 605846

    Thanks for any and all help
    Attached Files Attached Files
    Last edited by Terrehpin; 01-12-2019 at 01:49 PM. Reason: changed title

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    16,572

    Re: Help highlighting cells in a date range

    It looks like you have a name in cell A2, so where is the grade really, and where does the date come into it?

    I can hardly read your screen shot, and it always helps if you attached a sample Excel workbook, rather than a picture of one.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as that doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    01-12-2019
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    2

    Re: Help highlighting cells in a date range

    Hi again,

    Sorry about that I've attached the workbook now. I meant C2 rather than A2 for the grade, each column from C to S is its own grade. The Date would be term and I hadn't added that yet as I wasn't sure which was the best way to go about it. For example

    Autumn 1 is between Sept 03 - Oct 20
    Autumn 2 is between Oct 21 - Dec 21
    Spring 1 is between Jan 03 - Feb 15
    Spring 2 is between Feb 22 - April 10
    Summer 1 is between April 24 - May 25
    Summer 2 is between June 02 - July 20

    So if today's date would fall into Spring 1 and for anyone on 40-60w+ to 40-60s+ would be considered on track and that box would be highlighted light green, ELG and ELG+ would be exceeding so highlighted bright green. 40-60b+ & 40-60w would be at risk of falling behind so would be yellow, and then lastly below 40-60 and 40-60b would be well below and highlighted red.

    I thought it'd be too difficult for conditional formatting so would need some sort of formula.

    Thanks again

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    6,044

    Re: Help highlighting cells in a date range

    Hello Terrehpin and Welcome to Excel Forum.
    This proposal employs helper columns (XFD) for each of the sheets monitored by columns C:S on the Main Sheet. It also employs helpers below the data currently in columns C:S on the Main Sheet.
    Note that the proposal is modeled for sheets L&A and U
    Column XFD is populated using: =INDEX(N$24:S$32,MATCH(B4,M$24:M$32,0),MATCH('Main Sheet'!C$35,N$23:S$23,0))
    Note that the references to M24:S32 are for a table that has been set up to track achievement vs. time of year, and that 'Main Sheet'!C$35 contains the manually entered time of year.
    On the Main Sheet C37:D67 are populated using: =INDIRECT("'"&C$1&"'!XFD"&ROW(4:4))
    The conditional formatting rules for C2:S32 reference the values in rows 37:67
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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