Calendars across workbooks

[richdms]

I'm not sure if I posted this in the right forum, so apologies if this should be moved.

I am trying to create a master annual leave calendar and struggling to make it work.

Essentially, we have 25 staff who all submit annual leave dates onto their own workbook which I then want to put on a central calendar which shows the dates everyone is off so we avoid clashes (each dept. can only have 1 person off at any given time). I can provide an example if that helps, but what I want to have is:

Member of staff inputs date into their own workbook (or sheet)
On the central calendar their name appears on the dates they have booked off

Any help much appreciated

[Pete_UK]

Hi Rich,

It is always easier if all the data is in the same workbook, so can you arrange it to have all those individual sheets copied into the same (master) workbook? It is even easier if the "Leave Requests" are all contained in one sheet, rather than have one sheet for each individual member of staff - can that also be arranged?

You mentioned an example - it would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon for this, as it doesn't work on this forum.

Hope this helps.

Pete

[richdms]

Thanks Pete,

I can certainly put them in the same workbook but putting it all on one sheet would get messy I think. I believe I have attached a sample file. I am not set on the format of the calendar, its more the functions I need to use and I would like to learn more about doing this for my own practice. I tried with some IF functions and some VLOOKUP but couldn't get what I wanted from it.

[Pete_UK]

I've put something together which hopefully shows the advantage of having all the data on one sheet. I've just manually copied the name and start/end dates from your individual sheets into one combined sheet (columns A to C). Then I've used 5 other columns (E to I, coloured blue) which take the basic data and turns it into something that could be used by a calendar. Essentially, these formulae expand the date ranges into individual dates, and establish a unique reference to each.

I've then set up a planning chart type sheet (Plan_Cht) which has dates going across and names down, and any leave booked is shown in coloured bands (blue), so it is easy to compare the dates that staff have booked. In order to reduce the number of columns across, I have set this up to display 3 months' worth of data - you can set the year in cell A1 and the Quarter to display in cell A2, and the dates will automatically adjust to start at 1st January, 1st April, 1st June or 1st September.

In the Combined sheet I have copied the formulae down to row 100, but in practise you will probably need to copy them down further - notice that the 9 date ranges in columns A:C have expanded to row 57 (individual dates), so with 25 staff your real file (should you choose to base it on this one) will need many more rows to cover all the ranges.

Hope this helps.

Pete

[richdms]

Thanks for the help there Pete. I've not had a chance to have a proper look so once I have questions I'll come back!

One thing I do notice is around the quarters; is there a way to match this to a UK financial year? April to March rather than Jan to Dec?

[Pete_UK]

Yes, that was just something I put together quickly to demonstrate the principles.

I'll wait for your more detailed questions before amending it. Do you mean 6th April as the start of Q1, or will 1st April do?

Pete

[richdms]

The principles are great, I won't pretend to understand all the formulas going on but I will look them up to see what is going on. I agree that having a master absence sheet is the best option and this is only one step extra.

I will be adding days of the week to the master sheet as this is useful for us to see, as well as the helpful colour coding you've added, but can we change the counting function of absence to only cover work days? I am not sure how to do this.

1st April would be great as our holidays run from 1st April to 31st march, no matter the official start date of the financial year.

i was also looking at how this might be double coloured so we have a colour for holiday and one for sickness. These are the only two options we use in HR so 'h' or 's'. I assume a conditional formatting but again, not sure how I can do this.

What is the reference column doing in the formula? Just out of interest really.

Copying the formula beyond 100 rows will just work I assume? We have 24 current staff so actually the one you made is really good as a starting point!

Rich

[richdms]

Quick update Pete - we have decided it would be best to have a separate sickness and holiday calendar as sickness is confidential and holiday is not.
I'll just create a copy and label differently!

[Pete_UK]

Good job you told me - I had set it up for both.

I have made some other changes, so I'll just get rid of the things which relate to sickness and then post back.

Pete

[Pete_UK]

Okay, here's the file I've been working on.

You asked what the Ref column is for in the Combined sheet - this just adds the appropriate name to the date generated in column H (it shows as a serial number). Then the Planning chart has the days across the top and the names down the side so that can be re-generated to determine if the cell should be shaded or not.

In the Plan_Cht sheet, I've changed the formula in B4 so that Quarter 1 starts at 1st April. This means that Quarter 4 is in a different year than the one selected in A1, so A3 now shows the actual year that the dates relate to. I've also added the days of the week to the rest of row 3. I've not changed the dates to ignore weekends, but this can be done if you would like it - I show the weekends coloured green, so they stand out quite clearly. I'm not sure what you mean about a counting function for absence, but you can use this formula (say in CP6, then copied down):

=SUMPRODUCT((B6:CO6="x")*(WEEKDAY($B$4:$CO$4,2)<6))

This will only count highlighted cells which are weekdays, even if the display shows weekend absences. I've put this in for you.

Hope this helps.

Pete

[richdms]

Thanks Pete.

When I mean counting for absence is that on the combined sheet Column E counts the days of absence, but includes weekends in that. Is there a way to only include workdays in this counting?

Of course bank holidays will cause additional issues, but if there is a way to include UK national holidays as green that would be amazing!

[Pete_UK]

The formula that I put in CP6 does not include weekends in the count - check it out.

As for bank holidays, I'll do something on this later on - bit of gardening first.

Pete

[richdms]

I saw it in CP6 - is it possible to combine that on the 'combined' sheet to track days there? No issue if not!

Bank holidays would be amazing and you are a star.
Enjoy a spot of gardening first though!

[Pete_UK]

Hi Rich,

amended version of the workbook added below.

In the Combined sheet I have started compiling a list of holiday dates for public holidays in column N - I've filled it in for this year and for a few obvious ones for next year, but you can keep adding to this list as required (Note, the named range called holidays is defined as N$2:N$66, so if you add more than this you will need to use Name Manager to adjust the range).

I've inserted a couple of columns next to your data table. It occurred to me that your staff are making requests for leave when they submit the forms, and I presume they would need to be authorised by a supervisor or manager, so you could use column D (Status) for this. It would be possible to show "denied" or "pending" requests in a different colour on the planning chart, though I have not set it up for this. If you don't want to use it you can just delete the column.

I've also used column E to give you the number of actual working days that the leave request covers, with this formula in E2:

=IF(OR(B2="",C2=""),"-",NETWORKDAYS(B2,C2,holidays))

I've copied this down to row 100, as can be seen by the hyphens, which show where the formula is active. In another sheet you could have a summary of leave taken by a particular member of staff, using a SUMIFS formula. You can see that I've also added another test item (in red) in row 11.

On the planning chart itself I've used a pink colour to highlight the dates in rows 4 and 5 which are holidays, and have amended the formula in column CP so that the count only considers working days (i.e. excludes both weekends and holidays). Note, though, that this count is only for the quarter being displayed and not for the full leave year. (It might be better putting that in column B, rather than having to scroll all the way across).

Hope this helps.

Pete

[richdms]

Pete, this is perfect.
I appreciate the denied/pending column but this wont be held centrally as it is agreed by line managers before coming to us to input into the central system. I had also played with a few test items on a saved version and it all works perfectly.
This will save a huge amount of time and even if we manually tally the work days for each quarter this is a lot easier to do. Or we will do an end of quarter report in another sheet with holiday and sickness combined to give an overall attendance level; this I should be able to fix myself!

Thank you so much for your help, I hope one day to have these skills at my own disposal!

Rich

[Pete_UK]

Glad you are happy with it, Rich.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of any post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile). You can also leave a (private) message with this.

Pete