+ Reply to Thread
Results 1 to 4 of 4

Formula for Conditional formatting: today plus 5 working days

  1. 12-13-2016, 03:46 PM #1
    mkosa
    mkosa is offline
    Registered User
    Join Date
    08-06-2015
    Location
    London
    MS-Off Ver
    Mac Office
    Posts
    20

    Question Formula for Conditional formatting: today plus 5 working days

    Hi,

    I hope you are all well. Thanks for looking into this - much appreciated. I am trying to create a simple (wish it was that simple...) conditional formatting but I don't know what I'm doing wrong. I want to use three colours: yellow, green, red so excel prompts me if something is going to be due.

    Yellow - 5 working days before due date (only)
    Green - DUE TODAY
    Red - 5 working days AFTER due date (only)

    I attached the example where I coloured the cells. What formula do I need to use in order to achieve the same result with conditional formatting so that excel does it automatically?

    Any suggestions?
    Thanks
    Michelle
    Attached Files Attached Files
    _________________
    Best Wishes

    Michelle
    Reply With QuoteRegister To Reply
  2. 12-13-2016, 03:53 PM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,671

    Re: Formula for Conditional formatting: today plus 5 working days

    Do mean "Working days" or just days? Your example includes Saturday (10th Dec)and Sunday (11th Dec) as "working days".
    Reply With QuoteRegister To Reply
  3. 12-13-2016, 03:56 PM #3
    mkosa
    mkosa is offline
    Registered User
    Join Date
    08-06-2015
    Location
    London
    MS-Off Ver
    Mac Office
    Posts
    20

    Re: Formula for Conditional formatting: today plus 5 working days

    Hi, thanks for your reply John.

    Sorry, just working days - is there away to achieve this using just working days?

    Michelle
    Reply With QuoteRegister To Reply
  4. 12-14-2016, 01:52 AM #4
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,671

    Re: Formula for Conditional formatting: today plus 5 working days

    CF applied to column C (which has column A dates)

    for Today()-5

    =AND(NETWORKDAYS.INTL($C2,TODAY(),1,Holidays)-1>=1,NETWORKDAYS.INTL($C2,TODAY(),1,Holidays)-1<=5,WEEKDAY($C2,2)<=5)

    for Today

    =$C2=TODAY()

    For TODAY+5

    =AND(NETWORKDAYS.INTL(TODAY(),$C16,1,Holidays)-1>=1,NETWORKDAYS.INTL(TODAY(),$C16,1,Holidays)-1<=5,WEEKDAY($C16,2)<=5)

    Column D shows day of the week

    Column E has CF formulas

    Column G has named range "Holidays": just add any additions and change range in "Holidays" named range
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Issues with Conditional Formatting Formula - Blanks & TODAY Function
    By Shireen in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-02-2016, 03:54 PM
  2. [SOLVED] Conditional Formatting formula IF TODAY Between range of dates, color cell red
    By vayana in forum Excel General
    Replies: 5
    Last Post: 12-31-2013, 01:44 AM
  3. [SOLVED] Conditional formatting - highlight cell if it's empty 2 working days before today
    By uhtfgy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2013, 06:20 AM
  4. Conditional Formatting Formula to highlight TODAY
    By Jiptastic in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-30-2013, 10:42 AM
  5. Excel 2007 : today()-2 working days, in with other text
    By dancing-shadow in forum Excel General
    Replies: 6
    Last Post: 05-12-2011, 05:05 AM
  6. formula to get date Today-1 (Only working days)
    By Chetan Chauhan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-02-2010, 07:14 AM
  7. Add 4 working days to =Today()
    By piranhagirl in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-23-2005, 05:22 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1