how to link holiday tracker & calendar

[Harry Basra]

Hi guys,

I’m currently in the process of creating a holiday tracker/calendar. I’ve attached an example of the tracker below.

I use “sheet 1” to input the various holidays/sick days etc. for the all the employees.

This then feeds into the holiday tracker which calculates the individuals holiday balance.

I now want to data from Sheet 1 to feed into the January calendar. For example I want to see Johns Holiday shown in the calendar under Tuesday 3rd Jan. Does any one know how I can do this please or suggest a better way of executing this.

Many thanks

Harry

[Pete_UK]

I would suggest that you change the orientation of Sheet1 and make this a list where you enter the name of the employee, the type of leave and start and end date. You can then derive the calendar and the holiday tracker from this. For the calendar view, rather than have one sheet for each month, you might like to think about being able to select the month (and year) from a couple of drop-downs on a single sheet, and you could also have another drop-down to be able to filter the Team (e.g. Finance).

I have submitted a number of calendar files to the forum - check out the one attached to this thread:

http://www.excelforum.com/showthread.php?t=1097399

Hope this helps.

Pete

[Harry Basra]

Thanks for this Pete. I'm still struggling to get it to work.

[Pete_UK]

What do you need help with?

Pete

[Harry Basra]

Sorry how do i change the dates to 2017 - 2020? Also is it possible to input date ranges?

Thank you very much for your help, it is much appreciated.

[Pete_UK]

Cell K6 in the Calendar sheet contains a data validation drop-down to enable you to select the year - it is set up to allow you to choose from 2014 to 2019, but you can change this quite easily. Select that cell and click on Data | Data Validation (twice) and then you will see the list of years in the Source box, separated by commas. Just delete the ones you don't want and add others to the list, separating them by commas, then click OK.

I played about with this last night after posting to you and put your data into the file in the format required, so if you want to see that then let me know.

Hope this helps.

Pete

[Harry Basra]

Hi Pete,

Thank you very much. Yes please if you could show me that would be great.

This has been a massive help

[samba_ravi]

The Dates box in the calendar have merged cells so it is difficult to copy and paste the formula so please rove the merges and attach a new excel file

[Pete_UK]

Okay, file attached. You will see that I've added all the Bank Holidays for this year in the Leave_booker sheet and then listed the leave/sickness as they occur. You only need to record the name, leave_type and the start-date, but if it lasts for more than one day you can also record the end-date. The codes for the leave_types are defined at the bottom of the summary table at the end of the Calendar sheet, so you can change these to suit.

Hope this helps.

Pete

[Harry Basra]

This is brilliant, Thank you very much Pete, i've been trying to get this right for ages

Much appreciated

[Pete_UK]

Glad to help.

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

Also, 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 a 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).

Pete

[Harry Basra]

One last thing Pete then i'll leave you alone. I'm trying to move the totals table underneath the calendar to a different tab named "tracker". However when i copy the table over and try to change the formulas the table stops picking up the values in the leave booker.

I've tried to change the formula however it's still not working?!

Thank you

[Pete_UK]

Hi Harry,

Sorry for the delay in replying - I've been out. I think the attached file is what you are trying to do - I cut and pasted the table to another sheet (Tracker) and gave it a heading.

Note that this display changes with the month and year selected in the Calendar sheet, which is why I had it in that sheet in the first place. You might like to have a similar table below which shows the cumulative totals for the current year, so it is independent of the month chosen. If you take this route, however, you need to be aware that staff will usually book summer holidays well in advance, so if you are using this in April, say, the totals might look a bit distorted as the summer holidays will not have been taken by then.

Note also, that the leave_types are now defined in the Tracker sheet, rather than in the Calendar sheet.

Hope this helps.

Pete

[Harry Basra]

Thanks again Pete. Works great!


Is it possible to show the type of leave type in the calendar?. Currently it is only showing the name under the relevant date. However I’m trying to show the name and the type of leave.

I tried to amend the index match to pick up the “Name & Leave type” on the Leave booker. However I can’t get it to return both.

Is there anyway to do this. After this it should be complete

Many thanks

Harry

[Pete_UK]

If you display the full description of the type of leave as well as the name, then it might become a bit cramped on the calendar. If you only want to display the code for the type of leave, then I think the easiest approach would be to change the formula in cell K2 of the Leave_booker sheet to this:

=IF(OR(J2="",J2="-"),"",INDEX(A:A,J2)&" - "&INDEX(B:B,J2)&"")

and copy this down by double-clicking the fill handle on that cell. You might also want to remove the leave type in column B for the Bank Holidays.

As I said before, you can change the codes used for leave-types in row 26 of the Tracker sheet, so you can use something that makes sense to you.

Hope this helps.

Pete

[Harry Basra]

I managed to get this far last night. However the above formula doesn't feed into the tracker. It populates the calendar correctly, however its stopped feeding into the tracker now.
I've attached an example below.

Sorry to keep going on.

Many thanks

[avk]

Dear Harry
In your file some mismatch. I workout i tracker sheet & modified formula's (INDEX / MATCH/ COUNTIFS)
Also add new sheet "Holiday"
One clarification regarding in your sheet you mentioned "Discretion" column. Can you explain on what base calculated.
Plz look attach file.

[Pete_UK]

I've made the necessary changes to the Tracker sheet and copied the formulae down, so now you just need to list the names and their leave allowance in columns D, E and F.

I've also amended the Leave_booker sheet so that you now have drop-downs on the Name and Leave_type columns (A and B), and I've put some dummy data in to test out the Tracker sheet.

I think this is everything now - if so, please see Post #11.

Pete

[Harry Basra]

thanks for this AVK.

Do you know how i would exclude Saturday & Sunday from the calendar & tracker. As it is currently counting Sat & sun as a working day?

Many thanks

[Harry Basra]

thanks for all your help

[Harry Basra]

Hi Pete,

I was wondering whether you could help me on one last thing please?

I’ve noticed the calendar included Saturdays and Sundays when entering dates in the booker.

Is there anyway to amended the calendar to only show the leave type during the working week (Mon – Fri) at all?

Many thanks

Harry

[Pete_UK]

On the Calendar sheet you could just hide the columns for Saturday and Sunday (don't delete them, as this will mess up the formulae).

If someone books holiday for say Wednesday to the following Monday (inclusive), then you should enter this as from Wednesday to Friday and a separate entry for Monday, otherwise the summary table will include 2 days for the weekend.

Hope this helps.

Pete

[avk]

you can define those sat & sun.