Employee attendance tracker - viewing all together on a year calendar

Hello all,

I am guilty of using a prefab template from excel, it suits most of my needs except for one of my core requirements,
I refer to an employee attendance record,
I wish to use this and have managed to add in extra bits and pieces as I require, but I also need a sheet that displays all of the data in a long year with all of the employee's down the left so that it can be seen when multiple are on leave at the same time.

because of the nature of the template and its involved functions I have attached a copy of it.
as you will see I have put a bit of an example of a year to view in the sheet named as such at the bottom of what I hope to happen, I wish for all of that to be grabbed from the data that is on the "Employee Leave Tracker" Sheet

In short, I need all the data displayed inside the Calendar view, to consolidate into one long year calendar with all employees colored at once along it in the same color formats as the Calendar view.

Thanks so much for any help, I have been racking my brain but have come up empty.

Take a look at the attachment and see if it works for you.
I used the following formula for your yearly sheet (pasted in C6, then filled right, then down):

=IF(COUNTIFS('Employee Leave Tracker'!$B$4:$B$100,'Year to view'!$B$6:$B$10,'Employee Leave Tracker'!$C$4:$C$100,"<="&'Year to view'!C$5,'Employee Leave Tracker'!$D$4:$D$100,">="&'Year to view'!C$5,'Employee Leave Tracker'!$E$4:$E$100,"Sick Leave")>0,"S",IF(COUNTIFS('Employee Leave Tracker'!$B$4:$B$100,'Year to view'!$B$6:$B$10,'Employee Leave Tracker'!$C$4:$C$100,"<="&'Year to view'!C$5,'Employee Leave Tracker'!$D$4:$D$100,">="&'Year to view'!C$5,'Employee Leave Tracker'!$E$4:$E$100,"Bereavement")>0,"B",IF(COUNTIFS('Employee Leave Tracker'!$B$4:$B$100,'Year to view'!$B$6:$B$10,'Employee Leave Tracker'!$C$4:$C$100,"<="&'Year to view'!C$5,'Employee Leave Tracker'!$D$4:$D$100,">="&'Year to view'!C$5,'Employee Leave Tracker'!$E$4:$E$100,"Annual")>0,"A",IF(COUNTIFS('Employee Leave Tracker'!$B$4:$B$100,'Year to view'!$B$6:$B$10,'Employee Leave Tracker'!$C$4:$C$100,"<="&'Year to view'!C$5,'Employee Leave Tracker'!$D$4:$D$100,">="&'Year to view'!C$5,'Employee Leave Tracker'!$E$4:$E$100,"Compassionate")>0,"C",IF(COUNTIFS('Employee Leave Tracker'!$B$4:$B$100,'Year to view'!$B$6:$B$10,'Employee Leave Tracker'!$C$4:$C$100,"<="&'Year to view'!C$5,'Employee Leave Tracker'!$D$4:$D$100,">="&'Year to view'!C$5,'Employee Leave Tracker'!$E$4:$E$100,"Study/Workshop")>0,"W","")))))

The formula looks ugly, but it basically puts an "S" in cells for sick leave, "B" for bereavement, etc. It's set to consider leave entries on the Leave Tracker through row 100. You can change the 100s in the formula to something bigger if you anticipate more entries. Note that I also added 3 new entries in Leave Tracker for testing purposes. I then used to conditional formatting on C6:ND10 to change each letter to a color with, e.g. for Compassionate leave:

=C6="C"

I set the font and fill to the same color to get the solid box effect, but you can change the font to a different color if you want the letter to be visible.

I went with:

=5-COUNTBLANK(C$6:C$10)

...along the bottom to count your employees on leave. Let me know if it does the trick!

That is amazing, Sorry about the slow reply, I've been at work! (aus time)

I'm not so familiar with the counters for each of the leaves at the bottom of the single employee calendar, but it seems to not count any of the days if the dates specified run over the year, I can get around this by not extending past the 31st of December each year, and just starting a new leave cycle each time.
but is there any way to make it count what's in that year from the start to the end, even if the dates start or end outside of that calendar year that you know of?

Unfortunately, the template you chose is built around a calendar year. It was probably designed for businesses (like mine) that apportion all leave within calendar years. I don't know of a simple way to include dates for leave that crosses over into a new year. It can be done, but I'm thinking it will involve a few helper columns and may complicate what is (so far) a pretty straightforward template. The central issue is that we would need to tell Excel that if the leave dates are 12/24/16 to 1/5/17, we want 12/24 to be the start date if we're looking at 2016 but we want 1/1 to be the start date if we're looking at 2017. The same problem would exist with end dates.

Before I go that route, I'll note that the easiest solution (by far) would be to enter your leave within the calendar year, so sick leave from 12/24/16 to 1/5/17 would get two entries, one from 12/24/16 to 12/31/16 and another from 1/1/17 to 1/5/17. Problem solved. Is this method of entry possible for you, or do you want to explore something messier but all-encompassing?

Originally Posted by cantosh

Unfortunately, the template you chose is built around a calendar year. It was probably designed for businesses (like mine) that apportion all leave within calendar years. I don't know of a simple way to include dates for leave that crosses over into a new year. It can be done, but I'm thinking it will involve a few helper columns and may complicate what is (so far) a pretty straightforward template. The central issue is that we would need to tell Excel that if the leave dates are 12/24/16 to 1/5/17, we want 12/24 to be the start date if we're looking at 2016 but we want 1/1 to be the start date if we're looking at 2017. The same problem would exist with end dates.

Before I go that route, I'll note that the easiest solution (by far) would be to enter your leave within the calendar year, so sick leave from 12/24/16 to 1/5/17 would get two entries, one from 12/24/16 to 12/31/16 and another from 1/1/17 to 1/5/17. Problem solved. Is this method of entry possible for you, or do you want to explore something messier but all-encompassing?

Thank you again for your reply.
I think I'll leave it as it is and just ask that leave requests get split entries.
I spent some time adding in the leave request form with a macro to export as PDF and send to the boss based on the surname and save to HR file based on the same.
I added in the rest of the staff and another fillable couple of boxes instead of having to manually insert a row into the tracker table.
made it highlight school holidays and public holidays on the year to view.
All came out pretty well just in case anyone was curious about how it came out.

Thanks for the generous assistance cantosh, amazing work.

Glad I could help, good luck!