Lookup Schedule using multiple variables

[crispangilinan]

Hello! I need assistance in creating a formula that will lookup an employee's schedule based on:
1. Employee name
2. Schedule per Date range (start / end date)
3. Employee can have different schedule weekly that is why date range is important
3. Check if employee is on pto (start / end date)
4. Raw data indicates if the employee will work each day (E.g. Mon - Yes, Tues - Yes, Wed - No)

Technically, if i enter a date, the report should be able to output the employee schedule that day and tell if employee is on leave. We are talking about atleasr 300 employees. I would like to see all of their schedule depensing on the date i choose.

 

[ExceLoki]

can you share any sample data so we can help create a formula for you?

 

[crispangilinan]

Here you go. Sample Data. Schedule with End Date are Temporary Schedules while those without are long term schedules. The formula should be about to refer to the long term schedule if there are no temporary schedule falling within range. thanks for the help.

 

[ExceLoki]

i see several version of excel listed that you use. is there a particular wersionyou are needing this to work on?
also noticed in your sample data some employee #s have more than on name. is this the case in your actual data as well?

 

[crispangilinan]

i am actually using office 365 are well as google sheets. The employee can have multiple schedules (1) temporary with end date (2) long term with no end date. There are instances where the employee will follow a temporary schedule within a given date range and then revert back to the long term schedule once the temp schedule lapses. The table also has the days where the employee is expected to work. The one with Yes means they are supposed to work that day, and the ones with No means no work.

 

[crispangilinan]

i am actually using office 365 are well as google sheets. The employee can have multiple schedules (1) temporary with end date (2) long term with no end date. There are instances where the employee will follow a temporary schedule within a given date range and then revert back to the long term schedule once the temp schedule lapses. The table also has the days where the employee is expected to work. The one with Yes means they are supposed to work that day, and the ones with No means no work.

I think i made a mistake on the employee number of paul rod and nick balor. There should be a unique employee number per name. I corrected the data, thanks for noticing.

 

[ExceLoki]

are you wanting the schedule to show yes/no for each date or the start times?

currently i have it as yes/no

 

[ExceLoki]

hope this works for what you're wanting.
i added a helper column to the data table End Date Help
xl:2bb wouldnt let me copy everything, but you should be able to drag down the formulas for the rest

=IF(D2="",TODAY(),D2)

-------------------

Lookup Schedule using multiple variables.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Employee #	Last name, First name	Start Date	End Date	End Date Help	Start Time	Shift Hours	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
2	90001	Claire James	7/12/2022	8/11/2022	8/11/2022	6:00 AM EST	7	Yes	Yes	Yes	Yes	Yes	No	No
3	90001	Claire James	8/12/2022	9/5/2022	9/5/2022	5:00 AM EST	6	Yes	Yes	Yes	Yes	Yes	No	No
4	90001	Claire James	9/6/2022	9/26/2022	9/26/2022	11:00 AM EST	8	Yes	Yes	Yes	Yes	Yes	No	No
5	90001	Claire James	9/27/2022	1/15/2023	1/15/2023	9:00 AM EST	7	Yes	Yes	Yes	Yes	Yes	No	No
6	90001	Claire James	11/24/2022	11/28/2022	11/28/2022	9:00 AM EST	6	Yes	Yes	Yes	Yes	Yes	No	No
7	90001	Claire James	7/9/2022	7/17/2022	7/17/2022	12:00 AM EST	8	Yes	Yes	Yes	Yes	No	No	Yes
8	90002	Paul Rod	7/18/2022	9/12/2022	9/12/2022	12:00 AM EST	7	Yes	No	Yes	Yes	Yes	No	Yes
9	90002	Paul Rod	9/13/2022		2/3/2023	12:00 AM EST	6	Yes	Yes	Yes	Yes	Yes	No	No
10	90021	Nick Balor	5/30/2022		2/3/2023	9:30 AM EST	8	Yes	Yes	No	No	No	No	No
11	90003	Ablaza, Zhana	6/1/2022	6/1/2022	6/1/2022	1:00 PM EST	7	Yes	Yes	Yes	Yes	Yes	No	No
12	90003	Ablaza, Zhana	6/2/2022	6/22/2022	6/22/2022	12:00 PM EST	6	Yes	Yes	Yes	Yes	Yes	No	No
13	90003	Ablaza, Zhana	6/23/2022	11/6/2022	11/6/2022	11:00 AM EST	8	Yes	Yes	Yes	No	No	Yes	Yes
14	90003	Ablaza, Zhana	11/7/2022	11/13/2022	11/13/2022	11:00 AM EST	7	Yes	Yes	Yes	No	Yes	Yes	No
15	90003	Ablaza, Zhana	11/14/2022		2/3/2023	10:00 AM EST	6	Yes	Yes	Yes	Yes	Yes	No	No
16	90004	Abrera, Eman	1/28/2022		2/3/2023	8:30 AM EST	8	No	No	Yes	Yes	No	No	No
17	90005	Emily Smith	5/12/2022	5/13/2022	5/13/2022	9:30 AM EST	7	No	No	No	Yes	Yes	No	No
18	90005	Emily Smith	5/16/2022	10/3/2022	10/3/2022	8:30 AM EST	6	Yes	Yes	Yes	Yes	Yes	No	No
19	90005	Emily Smith	10/4/2022	10/4/2022	10/4/2022	9:30 AM EST	8	No	Yes	No	No	No	No	No
20	90005	Emily Smith	10/5/2022	10/31/2022	10/31/2022	10:00 AM EST	7	Yes	Yes	Yes	Yes	Yes	No	No
21	90005	Emily Smith	11/1/2022	11/6/2022	11/6/2022	8:00 AM EST	6	Yes	Yes	Yes	Yes	Yes	No	No
22	90005	Emily Smith	11/7/2022		2/3/2023	9:00 AM EST	8	Yes	Yes	Yes	Yes	Yes	No	No
23	90006	Michael Johnson	12/8/2022	12/9/2022	12/9/2022	4:30 AM EST	7	No	No	No	Yes	Yes	No	No
24	90006	Michael Johnson	12/12/2022		2/3/2023	10:00 AM EST	6	Yes	Yes	Yes	Yes	Yes	No	No
Raw Data

Cell Formulas
Range	Formula
E2:E24	E2	=IF(D2="",TODAY(),D2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:N131	Cell Value	="yes"	text	NO
H2:N131	Cell Value	="no"	text	NO

--------------------

Lookup Schedule using multiple variables.xlsx
	A	B	C	D	E
1	Employee #	Name	PTO Type	Start Date	End Date
2	90001	Claire James	Sick Leave	12/11/2022	12/12/2022
3	90005	Emily Smith	Annual Leave	12/15/2022	12/15/2022
4	90007	Jacob Davis	Emergency Leave	12/14/2022	12/14/2022
5	90019	Harper Wright	Annual Leave	12/11/2022	12/15/2022
6	90017	Amelia Nelson	Annual Leave	12/12/2022	12/12/2022
PTO

Cell Formulas
Range	Formula
A2:A6	A2	=XLOOKUP(B2,'Raw Data'!$B$2:$B$131,'Raw Data'!$A$2:$A$131,,0)

-------------------

Lookup Schedule using multiple variables.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Day	Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday
2		Date	12/11/2022		12/12/2022		12/13/2022		12/14/2022		12/15/2022		12/16/2022		12/17/2022
3	Employee #	Name	Schedule	Shift Hours	Schedule	Shift Hours	Schedule	Shift Hours	Schedule	Shift Hours	Schedule	Shift Hours	Schedule	Shift Hours	Schedule	Shift Hours
4	90001	Claire James	Sick Leave	0	Sick Leave	0	Yes	7	Yes	7	Yes	7	Yes	7	No	0
5	90002	Paul Rod	No	0	Yes	6	Yes	6	Yes	6	Yes	6	Yes	6	No	0
6	90021	Nick Balor	No	0	Yes	8	Yes	8	No	0	No	0	No	0	No	0
7	90003	Ablaza, Zhana	No	0	Yes	6	Yes	6	Yes	6	Yes	6	Yes	6	No	0
8	90004	Abrera, Eman	No	0	No	0	No	0	Yes	8	Yes	8	No	0	No	0
9	90005	Emily Smith	No	0	Yes	8	Yes	8	Yes	8	Annual Leave	0	Yes	8	No	0
10	90006	Michael Johnson		0	Yes	6	Yes	6	Yes	6	Yes	6	Yes	6	No	0
Expected Output

Cell Formulas
Range	Formula
A3:A37	A3	=UNIQUE('Raw Data'!A1:A131)
B4:B10	B4	=VLOOKUP('Expected Output'!A4,Data[[Employee '#]:[Last name, First name]],2,FALSE)
C4:C10,E4:E10,G4:G10,I4:I10,K4:K10,M4:M10,O4:O10	C4	=IFERROR(XLOOKUP(1,(C$2>=PTO[Start Date])*(C$2<=PTO[End Date])*(PTO[Employee '#]=$A4),PTO[PTO Type],INDEX(XLOOKUP(1,(C$2>=Data[Start Date])*(C$2<=Data[End Date Help])*(Data[Employee '#]=$A4),Data[[Monday]:[Sunday]],XLOOKUP(1,(C$2>=Data[Start Date])*(C$2<=Data[End Date Help])*(Data[Employee '#]=$A4),Data[[Monday]:[Sunday]],"")),1,WEEKDAY(C$2,2))),"")
D4:D10,F4:F10,H4:H10,J4:J10,L4:L10,N4:N10,P4:P10	D4	=IF(C4="yes",XLOOKUP(1,(C$2>=Data[Start Date])*(C$2<=Data[End Date Help])*(Data[Employee '#]=$A4),Data[Shift Hours],0),0)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O4:O37	Cell Value	="yes"	text	NO
O4:O37	Cell Value	="no"	text	NO
M4:M37	Cell Value	="yes"	text	NO
M4:M37	Cell Value	="no"	text	NO
K4:K37	Cell Value	="yes"	text	NO
K4:K37	Cell Value	="no"	text	NO
I4:I37	Cell Value	="yes"	text	NO
I4:I37	Cell Value	="no"	text	NO
G4:G37	Cell Value	="yes"	text	NO
G4:G37	Cell Value	="no"	text	NO
E4:E37	Cell Value	="yes"	text	NO
E4:E37	Cell Value	="no"	text	NO
C4:C37	Cell Value	="yes"	text	NO
C4:C37	Cell Value	="no"	text	NO

 

[crispangilinan]

Whoa! Thanks mate! I will give this a try and see what happens! Appreciate the help!

 

[ExceLoki]

happy to help. let me know if this ends up working for you.