• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 790
  • Last Modified:

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

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
Daniele Questiaux
Daniele Questiaux
  • 5
  • 3
1 Solution
Does it need to be an Excel Formula?
Can it be a UDF (User Defined Function using VBA)?
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)))
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Daniele QuestiauxResearch associateAuthor Commented:
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!

Daniele QuestiauxResearch associateAuthor Commented:
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!
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
Daniele QuestiauxResearch associateAuthor Commented:
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?

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now