Gantt chart using "REPT" in excel 2003 and showing public holidays

Posted on 2011-05-09
Last Modified: 2012-05-11
Good afternoon!
I am helping a friend build up a gnatt chart but am struggling.
As it is supposed to run for several years, to limit the use of columns, I am using one cell per week and try using "REPT"  with blanks (" ") and bars ("¦") to show the number of days worked. The blanks are there to align the bars either to the right or to the left depending on whether the first part of the week is worked, or the second part only.
I use networkdays which allow for public holidays. However, with the long formula I came up with, I have not managed to show the public holidays themselves as blanks as the formula was getting so long.
 I was wondering whether by some clever trick the formula could be reduced dramatically AND allow the public holidays to show up!
I am asking much, I know.
I find the date formulas extremely hard to handle most probably because I do not use them often enough.
I would appreciate any help.Thanks!
PS I am adding a short version of the gnatt chart with the bars shown as little rectangels to make it easier to see the number of days. example-gnatt.xls
Question by:daniques
    LVL 10

    Expert Comment

    Does it need to be an Excel Formula?
    Can it be a UDF (User Defined Function using VBA)?
    LVL 10

    Expert Comment

    This is the appropriate Excel Formula.
    Havent worked it to Put a space for Holidays

    =IFERROR(REPT(" ",MAX($B2,F$1)-F$1)&REPT("|",NETWORKDAYS(MAX($B2,F$1),MIN($C2,F$1+6),Holidays)),"")

    NETWORKDAYS needs the Analysis Toolpak (by the looks of your formula, you already have this)
    IFERROR needs Excel 2007 or higher.

    For Excel 2003 and below use
    =IF(ISERROR(REPT(" ",MAX($B2,F$1)-F$1)&REPT("|",NETWORKDAYS(MAX($B2,F$1),MIN($C2,F$1+6),Holidays))),"",REPT(" ",MAX($B2,F$1)-F$1)&REPT("|",NETWORKDAYS(MAX($B2,F$1),MIN($C2,F$1+6),Holidays)))
    LVL 10

    Accepted Solution

    And for a UDF use
    Option Explicit
    Function MyGnatt(ByVal StartDate As Date, ByVal EndDate As Date, ByVal MondayDate As Date, ByVal Holidays As Variant) As String
    Const NoDay = "N"
    Const YesDay = "Y"
    Dim MyHol As Collection
    Dim i As Variant
    Dim j As Variant
    Set MyHol = New Collection
    'Fill Holidays Collection
    'Excel Range
    If Not IsMissing(Holidays) Then
      On Error Resume Next
      If IsObject(Holidays) Then
        For Each i In Holidays
          MyHol.Add i.Value, Format(i.Value, "DD/MM/YYYY")
        Next i
      ElseIf IsArray(Holidays) Then
        For i = LBound(Holidays) To UBound(Holidays)
          MyHol.Add Holidays(i), Format(Holidays(i), "DD/MM/YYYY")
        Next i
      End If
      On Error GoTo 0
    End If
    On Error Resume Next
    'Loop through this week
    For i = MondayDate To MondayDate + 6
      'Try and get The Holiday of i
      'If it doesn't exist, it will error
      j = MyHol.Item(Format(i, "DD/MM/YYYY"))
      'Date is within Range
      'Date is not weekend
      'Date is not Holiday
      If i >= StartDate And _
          i <= EndDate And _
          WorksheetFunction.Weekday(i, 2) < 6 And _
          IsEmpty(j) Then
        MyGnatt = MyGnatt & YesDay
        MyGnatt = MyGnatt & NoDay
      End If
      j = Nothing
    Next i
    End Function

    Open in new window

    Sample Attached

    Author Comment

    Thanks you so much!

    The UDF is perfect. I just tweaked so that only the week days appeared ( Monday + 6 became Monday + 4). The formula was also quite intersting... but the UDF is so much simpler and cleaner.

    I changed the Y and N to bars and spaces but they do not have the same width so was wondering  
    ( wishes, wishes) whether the "Y" a block bar using Alt+219 (that is what I used i the UDF) formatted in a colour by choice, and the "N" , the same block bar but this time formatted either as white, or with no colours ( I am not sure that exists), so that the spacing can be consistent through all the cells, and the alignment perfect?

    Many thanks!


    Author Closing Comment

    yes, the solution was complete, accurate and very easy to follow!
    The use of a UDF is much easier to implement and easier to use that the long formula which was also provided, though it did not allow for the holidays but seriously reduced in length the formula I had come up with.
    Many thanks!
    LVL 10

    Expert Comment

    Thanks for the Points.

    Yes it can be done, with some un-preferable changes

    This will cancel it from being a formula and make it a Method (Needing to be re-run with every change)

    From what i understand you want a string of | in different colours based on whether its a working day.
    Y's in Black
    N's in White (invisible)

    This can only be done on a Cell basis, not a character by character basis

    Author Comment

    I am trying to keep the width of the columns equal, and so what I was hoping was to have the spacing for either the bars or the spaces equal in width pixel wise.
    If both the bars and the spaces were of equal width, then the entries would all be consistent and the alignment would be perfect.
    I have no idea how one can determine the width in pixels of a character, then assign a space of a given number of pixels to achieve this, but I suspect that would definitely have to be done with VBA.
    If the rept characters do not align correctly, the lines will be "messy".
    I have read a few blogs were people used the rept to that effect but I cannot find the actual method to remain consistent width wise.
    I thought keeping the same characters but different colours, but as you mentioned, that is impossible. Any other possible way to retain the same width for whatever entries of N or Y?

    LVL 10

    Expert Comment

    One of the problems with determining pixel size is the font.
    Each font would have a different size.
    If you were to use a mono-spaced font (like Consolas or Lucida Console) they would be in perfect alignment.

    Mono-spaced fonts have all letters and characters the exact same width in pixels.
    So the space is the same as the pipe bar is the same as the letter N

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now