Gantt chart question

[jazzkid]

Hi

I am using a gantt chart for the rostering staff to be able to plot the annual leave for all staff.

In order to make this easier, I was wanting to have a formula to calculate the first and last day of leave from the project start date in the inputs tab if that is possible. This means that the rostering staff just need to know the first and last dates of someone's leave - not to have to determine the number of the days when the leave commences and finishes.

Also - if staff have a number of dates for annual leave, which are not consecutive, is it possible to have the chart so that all the dates for one person is on one row? For example, on the attached chart, MA has three annual leave dates - all noted on different rows. Can they be put onto one row- I can't think how this would be done, but the staff I have been showing this to have asked if that is possible.

Any suggestions very gladly accepted.

 

[Hui]

Put dates in C6:D22
E6: =MATCH([@Column163],$H$2:$FS$2,0)
F6: =MATCH([@Column162],$H$2:$FS$2,0)
Copy both down

 

[vletm]

... something like this:
mark 'dates' with color and get begin-end days (including sum).
ideas ... questions?

 

[jazzkid]

Put dates in C6:D22
E6: =MATCH([@Column163],$H$2:$FS$2,0)
F6: =MATCH([@Column162],$H$2:$FS$2,0)
Copy both down

Thanks Hui

Must be something incorrect with my understanding of this.

I have done this for part of the chart and attached what the result is? I have left the original tab and created a 2nd tab "Respite Grant Gantt Chart (2)" where I did it for first 8 people.

What did I miss?

Much appreciate the assistance.

Put dates in C6:D22
E6: =MATCH([@Column163],$H$2:$FS$2,0)
F6: =MATCH([@Column162],$H$2:$FS$2,0)
Copy both down

 

[jazzkid]

... something like this:
mark 'dates' with color and get begin-end days (including sum).
ideas ... questions?

Thanks for this.

I am having issues with the spreadsheet. You have saved it as an xlsb chart and I can't figure out what the formulas are to get the colours in the chart. Also, I suspect this would be a bit beyond the skills of the people who have to do the annual leave scheduling. I am trying to make it as simple as possible. Essentially trying to get it so they need to put in the person's name and the first and last dates of the annual leave.

Thanks again.

 

[vletm]

@jazzkid
1) This sample have to be like .xlsb -format.
2) There are formulas in sheet only with those 'dates', no need more!
3) 'Colors': as I tried to write ... mark/fill colors of leaves and get dates
(NOT as You did!) You can mark/fill as many parts as need with those colors.
4) For me, mark/filling colors would be more safe that start to check many kind of marks of dates.

 

[jazzkid]

Thanks Vletm

Apologies but my excel skills are not strong enough to understand what you are trying to explain to me or to understand the spreadsheet you kindly posted.
If it is possible to explain this differently that would be great.
I really have no idea what you are saying in points 2 - 4

 

[jazzkid]

Put dates in C6:D22
E6: =MATCH([@Column163],$H$2:$FS$2,0)
F6: =MATCH([@Column162],$H$2:$FS$2,0)
Copy both down

I tried this again Hui and this time it worked. Can't explain why - unless it's the versions of Excel I am using? Earlier in the day I was using 2007, now I'm at home using 365 online version.

Thanks again for your assistance with this.

 

[vletm]

@jazzkid - okay, I'll try
2) formulas; Your original file has 1296 time formulas like
=IF(AND(COLUMNS($H$6:H6)>=$E6;COLUMNS($H$6:H6)<=$F6);IF(COLUMNS($H$6:H6)-$E6<ROUND(($F6-$E6+1)*$G6;0);fillblock;"");"") , Okay?
I deleted those and 'three row date formulas' too, Okay?
No need in this version!
3) 'Colors': as I tried to write ... mark/fill colors of leaves and get dates:
This version: Try to do next ...
a) select LF's 05/10-07/10 cells from sheet (calendar part)
b) select wanted background color
c) select another cell
=> You will get 'days of leave'
You can mark more days as You need, just repeat a-c.
4) You wrote: I am trying to make it as simple as possible. Essentially trying to get it so they need to put in the person's name and the first and last dates of the annual leave.
I wrote: For me, mark/filling colors would be more safe that start to check many kind of marks of dates.
For me, to mark some area and set color to it is more simple than writing two dates ... and You liked to get more 'days of leave' to show to one row. This is possible with this too.
Did You test to mark some are and so on?

 

[jazzkid]

@jazzkid - okay, I'll try
2) formulas; Your original file has 1296 time formulas like
=IF(AND(COLUMNS($H$6:H6)>=$E6;COLUMNS($H$6:H6)<=$F6);IF(COLUMNS($H$6:H6)-$E6<ROUND(($F6-$E6+1)*$G6;0);fillblock;"");"") , Okay?
I deleted those and 'three row date formulas' too, Okay?
No need in this version!
3) 'Colors': as I tried to write ... mark/fill colors of leaves and get dates:
This version: Try to do next ...
a) select LF's 05/10-07/10 cells from sheet (calendar part)
b) select wanted background color
c) select another cell
=> You will get 'days of leave'
You can mark more days as You need, just repeat a-c.
4) You wrote: I am trying to make it as simple as possible. Essentially trying to get it so they need to put in the person's name and the first and last dates of the annual leave.
I wrote: For me, mark/filling colors would be more safe that start to check many kind of marks of dates.
For me, to mark some area and set color to it is more simple than writing two dates ... and You liked to get more 'days of leave' to show to one row. This is possible with this too.
Did You test to mark some are and so on?

Thanks Vletm - think I got it. Will run it by others and see how they go with it.
Really appreciate your comments and explanations.

 

[jazzkid]

Put dates in C6:D22
E6: =MATCH([@Column163],$H$2:$FS$2,0)
F6: =MATCH([@Column162],$H$2:$FS$2,0)
Copy both down

Hi again Hui - or any others who would like to comment

When I do what Hui has suggested in Excel 2007 which is the version used at work, nothing happens?

I have uploaded what I have done - but did not do the whole sheet.

Any suggestions? All gladly accepted. I am really frustrated with this as I can get it to work at home on excel 365 and/or excel 2016.

 

[Chihiro]

Not sure what the issue is. But try replacing [@Column163] with $C6 in E6 and [@Column162] with $D6 in F6.

I'd also recommend nesting the formula in =IF($C6="","",formula). So that you don't see that pesky #N/A error when C & D columns are blank.

 

[Hui]

See my version of your original file

 

[jazzkid]

See my version of your original file

Thanks Hui

I have to learn better formula skills

 

[jazzkid]

See my version of your original file

Hui, this was wonderful and works well - however, when we showed it to management and printed out what we have for all the staff, they didn't like it and we found that it is a bit cumbersome

To put it into perspective, there are about 150 staff in four separate areas. All need to have leave each year. And of course, some months there are about 4 people who want leave and other months, there are a lot more - can be up to 15

The file will be printed, so that staff can see how many others are on leave, as only a certain number can be on leave at any time, e.g. for the senior staff - there are about 15 - only two can be on leave at the same time. So staff can easily see if there are already the maximum number of allowed staff on leave.

If I use the file which I was working with, we can really only print a month to a page, but this becomes a bit over the top with the amount of paper and wall space.

It was suggested, by senior management, that something like the attached would be better - for looking at - not necessarily for inputting data as far as I can see. There are no formulas in this chart - I couldn't figure where to start with them.

I am sure that the type of chart which Vletm suggested would work - but I was trying to get something more automated so the staff who manage the leave don't have such a tedious job

Any assistance/suggestion/reason why this isn't possible to achieve in excel would be greatly appreciated

 

[Hui]

Give me a few days

 

[jazzkid]

Thanks Hui

You're a legend

 

[jazzkid]

Give me a few days

Hi Hui

I am back at work tomorrow and know that I'll be asked about my progress with this. Do you have an update on how long this will be?

Thanks - I really appreciate your input with this.

 

[Hui]

Have you had a look at:
http://chandoo.org/wp/2013/01/24/employee-vacations-tracker-dashboard/

 

[jazzkid]

Have you had a look at:
http://chandoo.org/wp/2013/01/24/employee-vacations-tracker-dashboard/

Thanks Hui, I just did and I don't mind it, however, we need the specific dates the individual staff are on leave on the dashboard to be able to manage the amount of staff and who is on leave when.
Also I'm not sure that management would like it as they want the type I posted on Wednesday. The main manager is quite set on what she wants and is not open to alternatives at this stage unfortunately.
Will run it by the person inputting the data and see what they think and get back to you tomorrow.
Thanks for your assistance, sharing of knowledge and patience.

 

[Hui]

Please try this
Enter data into the Columns B,C,D & E at the top area only
Press the Button to Show Active Holidays only
Press the Button to Show all

It needs some cleaning up but works well
It only has 6 months ahead of the chosen date on the input sheet

 

[Hui]

You may want to pass this comment onto your manager

"Also I'm not sure that management would like it as they want the type I posted on Wednesday. The main manager is quite set on what she wants and is not open to alternatives at this stage unfortunately."

Please remember that you are getting this service for free

If your manager wants to pay us for the work, you get the option of being more selective in your requests

 

[jazzkid]

Please try this
Enter data into the Columns B,C,D & E at the top area only
Press the Button to Show Active Holidays only
Press the Button to Show all

It needs some cleaning up but works well
It only has 6 months ahead of the chosen date on the input sheet

Thanks Hui

I really do appreciate this.