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!
Danèle
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
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!
Danèle
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
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 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("
Havent worked it to Put a space for Holidays
=IFERROR(REPT(" ",MAX($B2,F$1)-F$1)&REPT("
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("
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
ASKER
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!
Danièle
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!
Danièle
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.
i.e.
YYNYYNN
Y's in Black
N's in White (invisible)
This can only be done on a Cell basis, not a character by character basis
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.
i.e.
YYNYYNN
Y's in Black
N's in White (invisible)
This can only be done on a Cell basis, not a character by character basis
ASKER
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?
Thanks!
Danièle
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?
Thanks!
Danièle
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
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
Can it be a UDF (User Defined Function using VBA)?