Register
Log In
Unanswered
Home
December 22, 2016
Offline
Hello All 🙂
I am new here, so thanks in advance for the good will, and thanks to Mynda for the kind help thus far!
I am using the Cond. formatting project /gantt chart and have a couple of things that I will be happy to learn how to do
As seen in the enclosed
screenshot
My questions are:
1. How do I make the blue formatting Not appear at the pattern formatted Fri & Sat ?
2. I tried to use an Office-holidays dates table in a dif. sheet and include it in the workday.intl function though I guess I've been doing it wrongly
3. could be nice (not as important as the above) to add a marker that shows 'current day' (like a red border line on the column of the current day
Thank you very much guys.
See Answer
Dashboards
June 25, 2016
Offline
Hi npr
Welcome to the board.
You should attach a sample file (not images) so that those who want to help you do not have to recreate the file themselves.
Attached is one way to get you started on what you wanted.
As for the marker, it is quite complex to create. What I have done instead is to highlight the date in yellow and draw a box if the date happens to be the same as your system's date (i.e. current date)
Hope this helps.
Sunny
December 22, 2016
Offline
Hi Sunny,
Thank you very much !
I will try to learn what/how you created it. Got you about attaching a sample. will do.
I see that you did not use Mynda's formula at the end date cell ( =IF(ISBLANK(G8),"",WORKDAY.INTL(G8-1,(I8+J8),7,Sheet3!D5:D6)) )
Best regards.
npr
Dashboards
June 25, 2016
Offline
There are different formulas that will give the same result, depending on your needs and how your data was set up.
Most important is that the formula (whatever it is) works for you. 
I got to go and get some sleep now (its already 1:00am over here).
CYA
Sunny
December 22, 2016
Offline
Thanks a lot and Good night.
December 22, 2016
Offline
Further to Sunny's recommendation, I attached the sample file
Thanks.
Dashboards
June 25, 2016
Offline
Hi npr
See if this is useful.
Sunny
Answers Post
December 22, 2016
Offline
Hi Sunny,
Thank you very much!
Yes, this allows me to incorporate the holidays.
Should I create a list of holidays in a separate sheet?
it has to be 2 columns? (name of holiday and date?)
npr
December 22, 2016
Offline
Hi Sunny,
I am interested to freeze both vertical and horizontal (green lines in the attached photo) and don't manage to.
When freeze one it cancel the other
Could you please also teach me how to do that ?
Thanks !!!
npr
if needed 
I can of course attache a file..
December 22, 2016
Offline
Hi Sunny,
I am trying to make the holidays work and it doesn't :(((
can you Please tell me what I'm doing wrong here ? (there's a 2 items holidays table in sheet 2)
Thank you very much.
Dashboards
June 25, 2016
Offline
Hi npr
To freeze the panels, do the following:
1) Select cell L16
2) From the ribbon select View - Freeze Panels - Freeze Panels
If it is currently frozen, you will have to unfreeze it first by selecting Unfreeze Panes then freeze them again using the above steps.
December 22, 2016
Offline
Hi Sunny,
Since I cannot upload an excel, in the file that you sent me (which has both vertical and horizontal freeze, and it's great)
Only the Holidays option does not work for me yet.
Placed dates in sheet2 though the gantt does not skip them.. 🙁
If you could help me on that, that will be Appreciated!
Thanks !
npr
it does uploads 🙂
December 22, 2016
Offline
Thanks for the Freezing instructions Sunny !
if I use the file you sent me, then it is perfectly designed already.
Only the holidays remain
Dashboards
June 25, 2016
Offline
Hi npr
See if this is what you are looking for.
I would suggest you create a dynamic range for your holidays.
Sunny
December 22, 2016
Offline
Dear Sunny,
Thank you!!
So The text in the formula must beeith the word holidays.. Holidays!E6:E7 ? 🙂
Thank you very much.
Dashboards
June 25, 2016
Offline
Dear npr
The formula is shown as Holidays!E6:E7 because I renamed the worksheet containing the holiday dates to Holidays otherwise the formula will be Sheet2!E6:E7
Sunny
December 22, 2016
Offline
Thanks Sunny!
Have a Good weekend.
December 22, 2016
Offline
I wish I could seat with you 2 min. with my laptop Sunny 🙂
Weird things as I touch (extend) your working example
When I do the holidays it does nor skip (add) the yellow dates
Also, I made a print of the formulas in your file, and I cannot understand the ~inconstancy of dates-ranges - - print is enclosed
Dates of holidays are selected with no $ sign I see.. that allows the range to be changed as one goes down the gantt sheet (??..)
It's no big deal operationally - I could just add the few holidays 'manually' - it's more of an interest to make it work 🙂
Thanks a lot Sunny.
Most Users Ever Online: 57
Currently Online: clark shao
12 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Sethu nath
Jose Alvarado
James Villapudua
Sheri O'Connor
Noreen Anagnos
Connie DeMercurio
Shanna Steinberg
Holly Seamons
Mark Cooper
David Matanana
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
