• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Conditional Formatting Gantt Chart

You are here: Home / Excel Charts / Excel Conditional Formatting Gantt Chart
excel conditional formatting gantt charts
May 28, 2013 by Mynda Treacy

Gantt charts are handy for planning and managing project tasks over time. They give a visual representation of the whole project, displaying progress to date and work to come. In this tutorial you'll learn how to build an Excel Gantt chart using Conditional Formatting. We’ll also look at how we can highlight tasks that are overdue, and the current date.

Excel Gantt Chart

Watch the Video

Subscribe YouTube


Excel Gantt Chart Workbook Download

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Completion Date Calculation - WORKDAY.INTL

Before we can apply the conditional formats, we need to calculate the completion date in column F. I’ll use the WORKDAY.INTL function as this enables me to specify which days my weekend falls on.

If you’ve got Excel 2007 or Excel 2003, you can use the WORKDAY function instead.

This ensures Excel doesn’t calculate a Saturday or Sunday as the completion date.

The syntax for WORKDAY.INTL is:

WORKDAY.INTL(start_date, days, [weekend], [holidays])

My formula in column F is:

=IF(ISBLANK(B5),"",WORKDAY.INTL(B5-1,D5+E5,1))

Which in English reads:

If B5 is blank then return a blank (this is because if I haven’t put a start date in for a task I don’t want it returning some crazy date in column F), otherwise take the start date in B5 less 1 day (so it doesn’t think the start date is already over), plus the completed days + remaining days, and by the way, we have Saturday and Sunday off so be sure to skip them.

Note: I’ve left the [holidays] argument blank but you can also reference a list of holidays you’d like Excel to skip.

Now for the Conditional Formatting….

Conditional Formatting for Gantt Chart

I'll be colour coding the cells as follows:

Gantt Chart Excel

The rules are applied to the date columns G:Z in the following order.

Gantt Chart Excel

Remember, Conditional Formatting formulas must evaluate to TRUE or FALSE. A TRUE outcome applies the format, whereas FALSE doesn’t.

When you enter your rule you enter it from the perspective of the first cell in your range. In my case this is G5. This is important when considering when to absolute or relative a reference, or part thereof.

Translated into English my formulas read:

Completed Days:

=AND($B5<=G$4,WORKDAY.INTL($B5-1,$D5,1)>=G$4)

Check that the Start Date (in B5) is less than or equal to the first date (in G4), AND calculate the days completed to date by taking the start date (in B5) less 1 + the Completed days (in D5) and check it’s greater than or equal to the first date (in G4). If both of these arguments are TRUE, colour the cell in dark green.

Estimated Days:

=AND($B5<=G$4,WORKDAY.INTL($B5-1,$C5,1)>=G$4,$C5=$E5)

Check that the Start Date (in B5) is less than or equal to the first date (in G4), AND calculate the end date by taking the start date (in B5) less 1 + the Estimated days (in E5) and check it’s greater than or equal to the first date (in G4), AND check that the Estimated number of days is = to the Remaining days. If all of these arguments are TRUE, colour the cell in light green.

Remaining Days:

=AND($B5<=G$4,WORKDAY.INTL($B5-1,$C5,1)>=G$4)

Check that the Start Date (in B5) is less than or equal to the first date (in G4),AND calculate the end date by taking the start date (in B5) less 1 + the Estimated days (in C5) and check it’s greater than or equal to the first date (in G4). If both of these arguments are TRUE, colour the cell in medium green.

Overdue Tasks:

=AND($B5<=G$4,WORKDAY.INTL($B5-1,$C5,1)>=G$4,$F5<$B$2)

Check that the Start Date (in B5) is less than or equal to the first date (in G4),AND calculate the end date by taking the start date (in B5) less 1 + the Estimated days (in C5) and check it’s greater than or equal to the first date (in G4). And check that the completion date is less than today's date in cell B2. If both of these arguments are TRUE, colour the cell in medium green.

Current Date:

=G$4=$B$2

Check that the current date in cell G4 is equal to today's date in B2. Tip: you could replace the reference to B2 with the TODAY function which will automatically return the current date based on your PC's clock, like so:

=G$4=TODAY()

Conditional Formatting Formulas Tips

Subscribe YouTube

 

Testing: I like to test my formulas in the workbook before creating my conditional formats. This allows me to quickly see if it's going to evaluate correctly by displaying the TRUE/FALSE outcomes.

Try it out when you download the workbook. Simply copy the formula from the Rules Manager into cell G5 and then copy it to the remaining cells in the chart (one at a time) to see how each one evaluates.

Perspective: As I said above, remember when you enter your new rule you enter it from the perspective of the first cell in your range when making references absolute.

Order: Another point to mention is that the order of the rules being applied is important. Rearrange their order in the Name Manager and see what happens.

You can learn more on how to work with formulas in Conditional Formatting here.

Project Management Dashboard

Take your skills one step further and learn how to create this Project Management Dashboard (click link for tutorial) using PivotTables, Conditional Formatting and Slicers for interactivity:

Project Management Dashboard

Feedback

If you liked this please show me by sharing it with your friends and colleagues on LinkedIn, Twitter, Facebook (or all of them 😉 ), or leave a comment below and tell me what you think. I read and reply to them all.

excel conditional formatting gantt charts

More Conditional Formatting Posts

conditional formatting tables and matrices in power bi

Conditional Formatting in Power BI Tables and Matrices

How to apply conditional formatting to tables and matrices in Power BI. Use color, icons, data bars and URL's.
project management dashboard

Excel Project Management Dashboard

Excel project management dashboard video tutorial covering various techniques including conditional formatting, PivotTables, Slicers, charts and more.
Highlight Selected Cell In Excel and Preserve Cell Format

Highlight Selected Cells in Excel and Preserve Cell Formatting

Use shapes to highlight the selected, active cells in Excel & preserve cell formatting. So you won't lose any conditional formatting, borders, colors etc.
Conditional Formatting PivotTables

Conditional Formatting PivotTables

Conditional Formatting PivotTables values areas will automatically expand/contract as you add new data or make changes to the filters, rows or columns.
Excel Conditional Formatting with Formulas

Excel Conditional Formatting with Formulas

Excel conditional formatting with formulas can be super confusing when you don't follow these three simple rules that ensure they work everytime.
Excel Factor 10 Conditional Formatting Painting by Numbers

Excel Factor 10 Conditional Formatting Painting by Numbers

Excel Factor 8 Highlight Cells Containing Formulas

Excel Factor 8 Highlight Cells Containing Formulas

Automatically highlight cells containing formulas in Excel to reduce the chance of them being deleted or over-written
How to Use Excel Conditional Formatting

How to Use Excel Conditional Formatting

Excel Conditional Formatting examples and workbook.

More Gantt Chart Posts

More Excel Charts Posts

excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.
stacked bar waffle chart

Stacked Bar Excel Waffle Charts

Stacked Bar Excel Waffle Charts are an alternate to using conditional formatting to build waffle charts, and some say they're easier.
Category: Excel ChartsTag: conditional formatting, Gantt Chart
Previous Post:Excel Gantt ChartExcel Gantt Chart
Next Post:Excel Formulasexcel formulas

Reader Interactions

Comments

  1. Gigster527

    November 17, 2021 at 5:16 am

    Can you use this in a Table?

    Reply
    • Mynda Treacy

      November 17, 2021 at 9:07 am

      Yes, you can modify it to work with a table rather than a PivotTable.

      Reply
  2. Tejas Desai

    June 10, 2021 at 11:38 pm

    Hello, This is a great Excel Conditional Formatting Gantt Chart.

    Where we three columns and colours to indicate Completed Days, Estimated Days & Remaining Days, But can we have one more colour and column which will have due days and colour will change after the actual date.

    For E.g. Start Date is 10Th June 2021 and say Est Dt: is 10 Days, So the completion Date will be 20th June 2021, But after 21st June 21, the cell colour should change as per the current to another colour indicating due days.

    I tried the formulas, but was not successful, can you or anyone help.

    Tejas Desai
    desaitejas@gmail.com

    Reply
    • Mynda Treacy

      June 11, 2021 at 10:05 am

      Hi Tejas,

      Please post your question on our Excel forum where you can also upload a sample file that illustrates what you’ve tried and we can help you further.

      Mynda

      Reply
  3. Rene Riegal

    February 13, 2018 at 12:30 pm

    Your formula does not seem to work for months since months have different number of days. Calendar is in months over multiple years. There is a start date & finish date. But since months end on 28, 29, 30 or 31 the formula does not seem to be so straightforward. Can you tell me how to construct your formula to work with months. Thank you.

    Reply
    • Catalin Bombea

      February 13, 2018 at 7:02 pm

      Hi Rene,
      It’s not just a formula that needs to be changed, you might need to make more related changes.
      Can you please upload a sample file with your desired structure? Use our Forum to create a new topic and upload the file.
      Regards,
      Catalin

      Reply
  4. Marc

    August 5, 2017 at 4:34 am

    Thank you for the information

    Is there a way to calculate half days ?
    As of now if I put a half day it will calculate only 2.

    If there is no way , then is there a way it should rather count it as 3 if the duration contains a half day.
    (Basically putting a condition on the B5-1 ,if it should – or not)

    Thank you

    Reply
    • Catalin Bombea

      August 6, 2017 at 2:38 pm

      Hi Marc,
      It is possible, of course. The layout will be different. What you want to see in columns? Hours? You will have to prepare a sample file with your desired outcome, the formulas needs to be adjusted and it will work.
      You can upload your sample file on our forum, don’t forget to describe what you want.
      Sign-up on our forum, create a new topic and upload your file, we will help you solve this one.
      Catalin

      Reply
  5. D Douglas Gruver

    January 5, 2017 at 4:12 pm

    I am having great difficulty in understanding how this works when all I can use is a start date, duration in weeks and percentage complete with dates that end in friday of each week. I need the duration in one color and the percentage complete in another color.

    Your help would be most appreciated.

    D Douglas Gruver

    Reply
    • Catalin Bombea

      January 7, 2017 at 3:50 am

      Hi Douglas,
      Can you please upload a file with a sample of your data to our forum? (create a new topic). It will be easier to understand your situation and to provide a personalized answer.
      Catalin

      Reply
  6. Jeff

    December 21, 2016 at 8:57 pm

    Dear Mynda Treacy,
    Thanks a lot !!!

    Could you please instruct how to make the conditional format NOT paint the 2 weekend days ?

    Thanks again

    Reply
    • Mynda Treacy

      December 22, 2016 at 11:58 am

      Hi Jeff,

      I’d cheat and add another Conditional formatting rule that filled the cells white if they were a weekend date. You can use this formula to check if the date in row 4 is a Saturday or Sunday:

      =OR(WEEKDAY(G$4,2)=6,WEEKDAY(G$4,2)=7)

      Make sure this new rule is at the top of the list of rules in your Conditional Formatting Manager.

      Mynda

      Reply
  7. B

    May 13, 2016 at 6:31 am

    Hi,

    Great post!

    Is there a way to build a gnatt chart that displays multiple time periods for each task?

    For example, Task 1 would have start date 1 end date 1, start date 2, end date 2, and so on. I would be tracking duration by days.

    Then, how would you setup conditional formatting so each task would have a unique color in the shaded graph?

    Thanks in advance!

    Reply
    • Mynda Treacy

      May 13, 2016 at 10:09 am

      Hi B,

      Yes, you could split your tasks over multiple rows or colour code components of different durations. This post explains how to use formulas in conditional formatting. Hopefully that’ll point you in the right direction so you can set up your rules as you want.

      Kind regards,

      Mynda

      Reply
      • B

        May 13, 2016 at 3:30 pm

        Thanks for the tip on color coding!

        As for my other question, I guess I wasn’t clear enough.

        Gannt charts can gaph a single tast and date on a single row.

        My problem is I have a single task, but I need to graph multiple dates on a single row for the single task.

        Is that possible?

        Reply
        • Mynda Treacy

          May 13, 2016 at 3:47 pm

          Hi B,

          As I said in my previous reply “Yes, you could split your tasks over multiple rows or colour code components of different durations“, meaning on the same row. Just as this example uses Conditional Formatting to create the chart, you’d do the same but you’d have to modify the Conditional Formatting rules to colour code different parts of the task in different colours.

          For example, instead of using different colours for ‘estimated days’, ‘completed days’ etc., you’d use different colours for your multiple date components.

          Mynda

          Reply
          • B

            May 14, 2016 at 5:21 am

            Hi Mynda,

            I’m still a little lost here.

            How would you get the chart to graph client “Ams” on a single line if the data looks like this?

            Client Start Duration End
            As 3/21/19 730 3/20/21
            Ams 3/21/19 91 6/20/19
            Ams 3/21/20 91 6/20/20
            Ams 2/21/21 27 3/20/21
            Ams 3/21/22 91 6/20/22
            Ns 3/21/19 91 6/20/19
            OPEN 6/21/22 272 3/20/23

            And, thanks for your patience! This is making my head hurt, don’t know how you do it!

            B

          • Mynda Treacy

            May 14, 2016 at 2:58 pm

            Hi B,

            If you want the conditional formatting on a single line then you need your data on a single line too. You’ll need to insert more columns so you can fit multiple start and end dates on the same row. For example, you’ll have columns for:

            Client
            Start 1
            Duration 1
            End 1
            Start 2
            Duration 2
            End 2
            Start 3
            Duration 3
            End 3
            etc.

            You then need to set your conditional formatting rule for each duration with separate colour coding. You can hide the start/duration/end columns when you want to print/view the chart.

            You should bear in mind that Excel is not a project management system, so if you want something complicated then maybe you should consider a proper project management application. e.g. Microsoft Project.

            Mynda

        • Chipster

          September 27, 2017 at 10:48 pm

          “B”… you are a real ungrateful jerk and moron. Mynda is going out of her way to show you something that is quite remarkable and all you can do is moan and groan. She’s giving you this info for free. No one has what she has in this example… jeez!

          Reply
  8. rochim

    February 23, 2016 at 12:34 pm

    Dear sir,
    this is awesome, could you let me know what if I also want to put saturday and sunday as well? please help to advise and teach how to change the formula

    Reply
    • Catalin Bombea

      February 23, 2016 at 4:32 pm

      Hi Rochim,
      All you have to do is to replace the formula for Completion Date column:
      =IF(ISBLANK(B5),””,WORKDAY.INTL(B5-1,(D5+E5),1))
      with:
      =IF(ISBLANK(B5),””,B5+D5+E5)
      Also, those 3 conditional formatting rules must be updated, you have to replace the WORKDAY formula:
      =AND($B5<=G$4,WORKDAY.INTL($B5-1,$D5,1)>=G$4)
      with:
      =AND($B5<=G$4,$B5+$D5>=G$4)
      Same adjustment must be made for each rule.
      Catalin

      Reply
  9. Igor Torrealba

    December 16, 2015 at 8:58 pm

    Hi, I am looking for a way to reflect a workflow of let’s say 10 tasks, like the one you had in your post, with a gantt chart like graphic, my drawback is that although the workflow is linear, a task can just happen one or more times, and I want that to be reflected, meaning, the whole process to be reflected, of course, not repeating the row for the repeated tasks, but just coloring the corresponding dates on the same row.

    Do you see a way to do such thing?

    Thanks!

    Reply
    • Catalin Bombea

      December 17, 2015 at 4:36 pm

      Hi Igor,
      If you can prepare a sample workbook with your data layout, it will be a lot easier to understand your situation and to provide a useful answer, rather than a generic answer.
      You can open a new ticket on our Help Desk.
      Cheers,
      Catalin

      Reply
  10. Marti

    August 25, 2015 at 5:07 am

    Beautiful Gantt chart. It does exactly what I need. Easy to add rows and columns. Thanks so much!

    Reply
    • Mynda Treacy

      August 25, 2015 at 9:11 am

      Thanks, Marti. Glad you found it useful 🙂

      Reply
  11. lori

    September 5, 2014 at 11:34 am

    hello. you seem to be the excel expert. I am looking to set up a gannet chart but having a week timeframe vs daily. I want to make my input based on the specific day then have the conditional format formula apply the color code in the appropriate week. help please.

    Reply
    • Catalin Bombea

      September 5, 2014 at 5:57 pm

      Hi Lori,
      Please use our Help Desk system to upload a sample file with your data. Don’t forget to include detailed informations, to help us understand your situation.
      Thanks for understanding
      Catalin

      Reply
  12. Devlin

    June 6, 2014 at 12:58 am

    Hi,
    Thank you very much; at first before reading your training material I had problem with Excel Formatting Gantt Chart, but now so far so good.

    Thank you,

    Devlin

    Reply
    • Mynda Treacy

      June 6, 2014 at 6:48 am

      That’s great to know, Devlin. Glad we could help.

      Reply
  13. David

    May 8, 2014 at 1:43 am

    You made my learning about gantt charts in excel easier and fun. Thanks for this post Mynda. My Last Post: Resources

    Reply
    • Mynda Treacy

      May 8, 2014 at 8:15 am

      Cheers, David.

      Reply
  14. John

    December 31, 2013 at 5:35 am

    Hi, Mynda. You do some fabulous things with Excel. I’m trying to create a technology roadmap to span a 5-year time period. So I’m interested in years and months rather than weeks and days.

    Can you tell me how I might be able to convert your sample Conditional Gantt chart to reflect years and months instead of weeks and days?

    Very much appreciated,

    -John

    Reply
    • Mynda Treacy

      January 1, 2014 at 2:09 pm

      Hi John,

      Thanks for your kind words 🙂

      The formulas in my Gantt chart use the WORKDAY.INTL function to increment time by days excluding weekends and holidays. If you want to increment your time/dates by months and years you don’t need to be as exact and can increment time by months/years.

      Instead of entering ‘days’ in columnds C, D and E you can enter months and in the formulas simply add 365/12 days for each month instead of using the WORKDAY.INTL function.

      Here a few functions that might also be of use to you:

      EOMONTH
      EDATE
      DATEDIF

      I hope that gives you some direction. Please let me know if you have any specific questions on implementing it.

      Kind regards,

      Mynda.

      Reply
  15. Neels Erasmus

    October 21, 2013 at 1:48 am

    Hi Mynda,

    Thank you very much for everything that you have contributed to make Excel an more useful tool! I am a pretty experienced user and invariably pick up something new from your applications, which I thoroughly enjoy.

    On the Gantt Chart topic, a closely-related issue is monitoring the execution of your planned project, so you ideally need both planned and actual bars in your graph. Added on top of that the requirement to be able to capture dependencies between the tasks, then you can easily see why dedicated project management software such as MS Project is there.

    But there is one application that I have done in Excel that is quite useful (I teach project management at University), and that is to construct a “Planned vs Actual” cumulative hours graph, which would typically be proportional to the cost expenditure on the project – it looks like these “worm” graphs of the cumulative runs scored by the two teams in a cricket match.

    I have made it to be generic, so you can enter your own set of tasks and planned vs actual durations and hours, and then it sums across these tasks at the end of every week to produce the graphs.

    I have deliberately stayed away from macros, but there is quite a neat application of array formulas in there to calculate the split when tasks “straddle” a week-end.

    I will be happy to send this to you to see if you want to share it on your hub if you would supply me with a direct e-mail address?

    Regards
    Neels

    Reply
    • Mynda Treacy

      October 21, 2013 at 9:23 pm

      Hi Neels,

      Thanks for your kind words 🙂

      Your planned vs actuals graph sounds interesting. I’d love to see it.

      You can send it to me via the help desk.

      Kind regards,

      Mynda.

      Reply
  16. JeffHT

    September 2, 2013 at 8:18 pm

    Hi,

    Just downloaded this s/sheet and it’s given me some great ideas as to how to solve some of my issues but i’m still hitting one problem that evades me.

    When producing the gantt chart how can I colour code the blocks to identify the weekends as different to the week days?

    I’ve tried all sorts of routines but nothing is consistent.

    Reply
    • Mynda Treacy

      September 2, 2013 at 8:23 pm

      Hi Jeff,

      You can use the WEEKDAY function to identify the day number of the date and then test whether that day number is the day for Saturday or Sunday like so:

      =OR(WEEKDAY($A2,2)=6,WEEKDAY($A2,2)=7)

      Where A2 contains your date being tested.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  17. Anand

    July 15, 2013 at 10:07 pm

    Hi,
    Thanks for this article. I’m not sure if the text and formulas match,
    In text, the reference is for E5 for estimated and remaining days, but the formula reads C5, Is this a typo? But the example download doesn’t seem to show errors.

    I haven’t had time to look into further!

    Reply
    • Mynda Treacy

      July 15, 2013 at 10:29 pm

      Hi Anand,

      I’m not sure which formula you’re referring to. The Estimated days has references to both E5 and C5. Can you please be more specific?

      Cheers,

      Mynda.

      Reply
  18. Jack D. Allen

    July 2, 2013 at 5:33 pm

    I have created a simple library excel spreadsheet for my childs Kindy and I am trying to add a few smarts to it. I have added a calendar control to populate active cells with dates however I am trying to populate a “Book Status” column with “IN”, “OUT” or “Overdue” based on whether certain date formulas are met. i.e if the “Borrowed Date” is blank or the “Return Date” is less than the “Return Due Date” then I require the “Book Status” cell to be populated with “IN”. If the “Return Due Date” is greater than the “Borrowed Date” then I require the “Book Status” cell to be populated with “Overdue”.

    Reply
    • Carlo Estopia

      July 3, 2013 at 9:58 am

      Hi Jack,

      My apologies if you have read my first comment.
      I thought I read “I have some smarts added to it”
      instead of “I am trying…”.

      Anyways, you may use a nested IF function for this one which is more or less like this one:

      =IF(AND(B2="",A2=""),"",IF(B2="","OUT",IF(OR(B2<A2,B2=A2),"IN","OVERDUE")))
      

      based on the assumed data:

      Borrowed Date	Return Date	Book Status(formula)
       1/1/2010	1/1/2010	   IN
      

      references:
      IF FUNCTIONS
      NESTED IF FUNCTIONS

      Cheers,

      CarloE

      Reply
  19. Pavel Lasák

    June 1, 2013 at 3:31 pm

    Thanks a for this interesting post.

    My article about Gant chart using conditional formatting in Czech language. For the reader who is this language beter than English. ( http://office.lasakovi.com/excel/grafy/ganttuv-diagram-excel/ )

    Reply
    • Philip Treacy

      June 1, 2013 at 8:27 pm

      Thanks Pavel, some interesting charts on your site.

      Cheers

      Phil

      Reply
  20. Lin

    May 31, 2013 at 5:18 pm

    Thanks a lot for this post

    Reply
    • Mynda Treacy

      May 31, 2013 at 7:54 pm

      You’re welcome, Lin 🙂

      Reply
  21. Etienne Baijot

    May 30, 2013 at 7:07 pm

    Hi Lynda,

    Thanks a lot for your inspiring work and enthusiasm.

    I have changed some dates in your chart, but the color did not change accordingly; for example, first line, if you replace 27/05 by 02/06 or 07/06, the two first cells remained unchanged whiel they should become light green.

    I could send you my Gantt chart more sophisticated than that, because a % of a task in not necessarily calculated on the basis of a number of day, but based on some specific indicators. However, working language in Senegal is French, so I guess that many readers would not understand it !

    Again thanks for your time, reactivity and thoughts,

    Etienne

    Reply
    • Mynda Treacy

      May 30, 2013 at 7:55 pm

      Hi Etienne,

      Thanks for your comments.

      When I change the date in my chart to 2nd June or 7th June it changes for me. Could it be that your computer date format is mm/dd/yy whereas mine is dd/mm/yy? Although your computer should convert the dates when you open the file.

      As I mentioned in my tutorial, my chart was really for the purpose of teaching how you could use Conditional Formatting to create a Gantt style chart and I’m sure you can make improvements or tailor it to your needs. Like you suggest, one improvement might be to use % completion of task indicators if days completed/remaining aren’t what you use to measure a project’s status.

      I’d love to see your chart although I’m not sure my version of Excel will be able to translate the language but it’s worth a try if you want to share it.

      Kind regards,

      Mynda.

      Reply
  22. Franee

    May 30, 2013 at 4:37 am

    You are awesome and never cease to amaze me.

    Reply
    • Mynda Treacy

      May 30, 2013 at 6:42 am

      Aw, thanks Franee 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x