Results 1 to 5 of 5

Thread: Conditional Formatting for a date
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2016
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional Formatting for a date

    Hello.

    I currently have conditional formatting to highlight a cell (E6) blue if the date in that is todays date, which works well. I now want to add conditional formatting that would highlight a cell (E6) if the date in that cell takes place during the current work week, and have it stop highlighting after that day has passed.

    For Example: If the date in cell E6 was 2017-01-19. On this work week, 11/16/17-11/20/17 E6 would be highlighted light blue, on 1/19/2017 it would be a dark blue (rule I already have in place), and it would go back to being un-highlighted (white) after the day 1/19/17 had passed.

    I can't remember how I set up the current conditional formatting rule to highlight todays date blue, but when looking at the rule it just says 'Today', and that is the only rule in column E.

    Thank you in advance.

  2. #2
    Board Regular chicagocomputerclasses's Avatar
    Join Date
    Mar 2015
    Location
    Chicago, IL
    Posts
    645
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for a date

    This should work.

    Code:
    =AND(WEEKNUM(E6,2)=WEEKNUM(TODAY(),2),WEEKDAY(E6,2)<6,YEAR(E6)=YEAR(TODAY()))

  3. #3
    Board Regular
    Join Date
    Aug 2016
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for a date

    Thank you! That all seemed to have worked, except if the date is in this workweek, and has already passed (1/16/2017)

  4. #4
    Board Regular chicagocomputerclasses's Avatar
    Join Date
    Mar 2015
    Location
    Chicago, IL
    Posts
    645
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for a date

    OK, I've missed that part in your explanation. Try this:

    Code:
    =AND(WEEKNUM(E6,2)=WEEKNUM(TODAY(),2),WEEKDAY(E6,2)<6,YEAR(E6)=YEAR(TODAY()),E6>=TODAY())

  5. #5
    Board Regular
    Join Date
    Aug 2016
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for a date

    That works perfectly. Thank you very much

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •