Tracking employee's holidays

[jberg123]

Hi all,

I have a bunch of identical looking excel sheets from all employees in our team listing their annual leaves/holidays.

Simplified, they contain a column A with start date and column B with end date, for each of their holidays during the year.
Each row represent a holiday during the year.
An employee could have any given number of holidays, each of different lengths.

Now, I want to insert all these excel sheets into a master document and want to show in a separate tab a grid with who's out of office when.
Each row would be an employee
Each column would be a different date (1 Jan, 2 Jan, etc... , 31 Dec)

How can I in a smart way show a 1 for out of office or 0 for in office for each of the dates and each individual?

The employee tabs will be named by their employee code call it CODE.

I'm struggling to do this without a seriously ugly set of IFs...

I guess for each employee we want to test if a given date is within any of the ranges provided on his/her tab.

Thank you!

 

[edmitchell]

Hi

I've built a test model as below, which I think is what you are after/need

Employee worktab contains (Joe is the name of the first one, and each other is a 'mirror image')

Joe
[TABLE="width: 0, align: left"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Holidays
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]From
[/TD]
[TD]To
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]06 January 2018
[/TD]
[TD]06 January 2018
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]14 January 2018
[/TD]
[TD]15 January 2018
[/TD]
[/TR]
</tbody>[/TABLE]












Group Summary
[TABLE="width: 0"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]01 January 2018
[/TD]
[TD]02 January 2018
[/TD]
[TD]03 January 2018
[/TD]
[TD]04 January 2018
[/TD]
[TD]05 January 2018
[/TD]
[TD]06 January 2018
[/TD]
[TD]07 January 2018
[/TD]
[TD]08 January 2018
[/TD]
[TD]09 January 2018
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Joe
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Fred
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Mike
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]



B2 contains this formula (which then needs to copied to each of the others calculated cell)
=SUMPRODUCT((--INDIRECT($A2&"!$A$3:$A$30")<=B$1)*(--INDIRECT($A2&"!$B$3:$B$30")>=B$1)*(--INDIRECT($A2&"!$A$3:$A$30")<>""))


the use of the 'INDIRECT' allows the formula to the same throughout the rows, e.g it looks up the name of the work tab that contains the employee data for his/her holidays for that row
the $A$3:$A$30 is the table/range that contains the rows for the holiday dates (so if there would be more than 3 to 30 (e.g 28 rows) then you would need to increase the row above 30).

works okay in my test model, so hope it works for you!

Regards

 

[jberg123]

Hi

I've built a test model as below, which I think is what you are after/need

Employee worktab contains (Joe is the name of the first one, and each other is a 'mirror image')

Joe
[TABLE="width: 0, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Holidays
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]From
[/TD]
[TD]To
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]06 January 2018
[/TD]
[TD]06 January 2018
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]14 January 2018
[/TD]
[TD]15 January 2018
[/TD]
[/TR]
</tbody>[/TABLE]












Group Summary
[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]01 January 2018
[/TD]
[TD]02 January 2018
[/TD]
[TD]03 January 2018
[/TD]
[TD]04 January 2018
[/TD]
[TD]05 January 2018
[/TD]
[TD]06 January 2018
[/TD]
[TD]07 January 2018
[/TD]
[TD]08 January 2018
[/TD]
[TD]09 January 2018
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Joe
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Fred
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Mike
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



B2 contains this formula (which then needs to copied to each of the others calculated cell)
=SUMPRODUCT((--INDIRECT($A2&"!$A$3:$A$30")<=B$1)*(--INDIRECT($A2&"!$B$3:$B$30")>=B$1)*(--INDIRECT($A2&"!$A$3:$A$30")<>""))


the use of the 'INDIRECT' allows the formula to the same throughout the rows, e.g it looks up the name of the work tab that contains the employee data for his/her holidays for that row
the $A$3:$A$30 is the table/range that contains the rows for the holiday dates (so if there would be more than 3 to 30 (e.g 28 rows) then you would need to increase the row above 30).

works okay in my test model, so hope it works for you!

Regards

Thank you! Extremely helpful and smart solution Thanks again