+ Reply to Thread
Results 1 to 2 of 2

Gantt chart using conditional formatting and several time intervals

  1. #1
    Registered User
    Join Date
    09-05-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    4

    Gantt chart using conditional formatting and several time intervals

    Hello everybody,

    I am trying to make a gantt chart using conditional formatting. The problem is that I sometimes have several time intervals within one day and I don't know if there's a function that can achieve this.

    Please see the preview in the attachment for easy understanding.

    A1:F11 is the data the chart is based on (columns B,C,E,F are additional and are created using functions LEFT and RIGHT - if it could be done without them, then that would be even better)
    Rows 14 to 19 is what I am trying to achieve (I changed the colour manually here)
    "Format values where this formula is true" = Here I am looking for a function that will check the date in the chart table (A14:A19), then search for the same dates in B4:B11 and return true if it finds one where the start time (in column C) in the same row is smaller or equal to the time in the heading of the chart (row 13) and the end time (in column F) is greater than this time.

    If there was just one time interval in one day and the dates above the chart were in ascending order, it would be easy and the "Format values where this formula is true" for example for the first row of this chart (B4:Y14) could be set as =AND(B$13>=$C11,B$13<$F11).

    Any idea how to achieve this when there are sometimes multiple time intervals within one day as shown in the preview?

    Thank you very much for your help.
    Attached Images Attached Images
    Last edited by Keple45; 09-06-2019 at 04:49 AM.

  2. #2
    Registered User
    Join Date
    09-05-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    4

    Re: Gantt chart using conditional formatting and several time intervals

    I've just solved this using the function COUNTIFS.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 09-04-2019, 05:36 PM
  2. [SOLVED] Conditional Formatting w/ Gantt Chart
    By ormont02 in forum Excel General
    Replies: 10
    Last Post: 02-24-2019, 11:55 PM
  3. Gantt Chart Conditional formatting
    By jsneak in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-30-2015, 07:19 AM
  4. Gantt Chart Conditional Formatting
    By tbrozovich in forum Excel General
    Replies: 6
    Last Post: 06-25-2015, 02:23 PM
  5. Conditional formatting to measure actual vs planned Gantt chart
    By arnab0711 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-19-2015, 04:19 PM
  6. Gantt chart for time and formatting
    By smf28374 in forum Excel General
    Replies: 1
    Last Post: 02-02-2011, 11:28 AM
  7. Gantt Chart with conditional formatting
    By douglasdale in forum Excel General
    Replies: 2
    Last Post: 06-28-2010, 11:24 AM

Tags for this Thread

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