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 QuestiauxResearch associateAsked:
Who is Participating?
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
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)))
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.