Who is in work question

[crangelag]

Hello,

I have a table with all the employees (employees) on it and another table (leave) with a list of leave or sicknesses on it as per below. It's taken that you are in work if you are not on table2.

Name|Type|Start_Date|End_Date
John Doe|Leave|25/12/24|26/12/24
Jane Doe|Sickness|25/12/24|28/12/24

How do I get Excel to report who is at work on a given date? It would be nice not just to use the TODAY() but also to input a date and see who is/was in work.

Many thanks,

C

 

[FormR]

Hi, welcome to the forum! Here is one way you could try to adapt to your specific set up.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Type	Start_Date	End_Date		All Employees		Given Date		In Work
2	John Doe	Leave	25/12/2024	26/12/2024		John Doe		26/12/2024		Frank
3	Jane Doe	Sickness	25/12/2024	28/12/2024		Jane Doe				Fred
4	Frank	Leave	01/12/2024	25/12/2024		Frank				Jeff
5						Fred
6						Jeff
Sheet1

Cell Formulas
Range	Formula
J2:J4	J2	=LET(emp,F2:F6,FILTER(emp,COUNTIFS(A:A,emp,C:C,"<="&$H$2,D:D,">="&$H$2)=0))
Dynamic array formulas.

 

[crangelag]

Worked like a treat. How would I also show the opposite (those not in work, and if possible the reason why)?

 

[crangelag]

I changed the formula to =LET(emp,F2:F6,FILTER(emp,COUNTIFS(A:A,emp,C:C,"<="&$H$2,D:D,">="&$H$2)=1)) and it is now giving me all the people not in.

Many thanks, @FormR

 

[FormR]

I changed the formula to =LET(emp,F2:F6,FILTER(emp,COUNTIFS(A:A,emp,C:C,"<="&$H$2,D:D,">="&$H$2)=1))

I guess an employee will never be "not in" for more than one reason, or have overlapping absent records, but just to be sure, I'd change the filter criteria to ">0" rather than "=1"

 

[FormR]

Of if you wanted the reason as well, you could filter the absence list directly (avoid using full column references in this instance):

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Type	Start_Date	End_Date		All Employees		Given Date		In Work		Not In
2	John Doe	Leave	25/12/2024	26/12/2024		John Doe		26/12/2024		Frank		John Doe	Leave
3	Jane Doe	Sickness	25/12/2024	28/12/2024		Jane Doe				Fred		Jane Doe	Sickness
4	Frank	Leave	01/12/2024	25/12/2024		Frank				Jeff
5						Fred
6						Jeff
Sheet1

Cell Formulas
Range	Formula
J2:J4	J2	=LET(emp,F2:F6,FILTER(emp,COUNTIFS(A:A,emp,C:C,"<="&$H$2,D:D,">="&$H$2)=0))
L2:M3	L2	=FILTER(A2:B100,(C2:C100<=H2)*(D2:D100>=H2),"")
Dynamic array formulas.