+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting, 3 or more weekdays less than today

  1. #1
    Registered User
    Join Date
    04-19-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Conditional Formatting, 3 or more weekdays less than today

    Hi,
    I have been playing around for a while now with network days, weekdays etc etc but can't seem to get a formula to work in Conditional Formatting.
    I need a cell to be highlighted red once the date entered is 3 or more working days less than today. (i.e missed deadline)
    In an ideal world, I would like it then to highlight green once a subsequent date is then entered in another cell, to show that the action has been performed.

    For example.
    Cell B2 contains date. Once this date is >3 working days less than today, it highlights red.
    Cell C2 is initially blank, but once cell B2 highlights red, a date should be entered in C2 to confirm action has been performed and therefore, cell B2 should now become green.
    Hope this makes sense, I am using Excel 2003

    TIA

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Conditional Formatting, 3 or more weekdays less than today

    Did you use

    =AND(C2="",NETWORKDAYS(B2,TODAY())>3)

    and

    =AND(C2<>"",NETWORKDAYS(B2,TODAY())>3)

  3. #3
    Registered User
    Join Date
    04-19-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Conditional Formatting, 3 or more weekdays less than today

    Bob,

    Thanks for your quick reply

    When trying that I get an error message saying 'You may not use references to other worksheets for Conditional Formatting Criteria' ? I believe this has something to do with Network days being a Toolpak addin? So that is whay I have been trying to use the weekday function.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Conditional Formatting, 3 or more weekdays less than today

    Try something like this

    =AND(C2="",CHOOSE(WEEKDAY(B2),3,3,5,5,5,4,3)+B2<TODAY())

    You might need to adjust the values depending on your exact definition of ">3 working days less than today"

    a simpler method would be to use WORKDAY formula on the worksheet, e.g. in any cell

    =WORKDAY(TODAY(),-3)

    then you can compare the B2 date to that date.....
    Audere est facere

  5. #5
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Conditional Formatting, 3 or more weekdays less than today

    Quote Originally Posted by Hippo_MC View Post
    When trying that I get an error message saying 'You may not use references to other worksheets for Conditional Formatting Criteria' ? I believe this has something to do with Network days being a Toolpak addin? So that is whay I have been trying to use the weekday function.
    OK> I have Excel 2010, and Networkdays is not in an addin anymore, so it works fine.

  6. #6
    Registered User
    Join Date
    04-19-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Conditional Formatting, 3 or more weekdays less than today

    Thanks, I seem to have the same error message with the Workday function as mentioned above, but the first formula works. Thanks for your help!

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Conditional Formatting, 3 or more weekdays less than today

    Quote Originally Posted by Hippo_MC View Post
    Thanks, I seem to have the same error message with the Workday function as mentioned above
    Yes, you'll get an error with WORKDAY in conditional formatting in 2003 the same as you would with NETWORKDAYS but my suggestion was that you use WORKDAY in a cell on the worksheet, e.g. put this formula in Z2

    =WORKDAY(TODAY(),-3)

    then refer to that cell in conditional formatting something like

    =AND(C2="",B2< Z$2)

+ 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