look up with date ranges

[ExcelNewbie2020]

i have this table in sheet 1 and sheet 2..I need a formula in sheet 1 (in yellow). that will auto populate based on the data in sheet 2 (in blue). wherein sheet 2 is the input table. I will input the "OFF" schedule that should appear in sheet 1... while the vacation (from start to end) should populate once the status is "OK"..Otherwise, "W" should appear.

4_Must-Have_AI_Tools (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1	SHEET 1																															SHEET 2
2			FEBRUARY 2023																																	VACATION (VL)
3			01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28			EMP.#	OFF		START	END	STATUS
4		EMP.#	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue		NAME4	329	Sat	Sun	06-02-23	08-02-23	OK
5	NAME1	326	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W		NAME5	330	Mon	Tue	15-02-23	17-02-23	OK
6	NAME2	327	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W		NAME6	331	Wed	Thu	10-02-23	12-02-23	OK
7	NAME3	328	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
8	NAME4	329	W	W	W	OFF	OFF	V	V	V	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF	W	W
9	NAME5	330	W	W	W	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF	V	V	V	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF
10	NAME6	331	W	W	W	W	W	W	W	OFF	OFF	V	V	V	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF	W	W	W	W	W
11	NAME7	332	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
12	NAME8	333	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
13	NAME9	334	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
14	NAME10	335	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W	W
Sheet2

 

[Anthony47]

Which is the meaning of the info in Sheet2 and what is there about EMP #326?

 

[ExcelNewbie2020]

Which is the meaning of the info in Sheet2 and what is there about EMP #326?

in sheet 2 you will find schedule "OFF" means the days they are off duty and the VACATION days with start to end..if the names/EMP.# did not show up in sheet 2 means they are "W" in sheet 1

 

[Anthony47]

The columns AH and AI are shown as a date in the XL2BB minisheet; for example AH4= 44968 = Feb 11. Is it a date or a String?
So it is not mandatory that each Employee has an entry in Sheet2, right?

 

[awoohaw]

Hi, I think i have a solution, but I think your sample has NAME6 first two days of February wrong. According to the rules they should be off:
(My solution is at the bottom, your original expecttions is unchanged and at the top (except for I deleted a lot of NAMES).

mr excel questions 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	SHEET 1
2			Feb-23
3			Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb
4		EMP.#	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
5	NAME4	329	W	W	W	OFF	OFF	V	V	V	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF	W	W
6	NAME5	330	W	W	W	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF	V	V	V	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF
7	NAME6	331	W	W	W	W	W	W	W	OFF	OFF	V	V	V	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF	W	W	W	W	W
8
9
10			SHEET 2
11							VACATION (VL)
12				EMP.#	OFF		START	END	STATUS
13			NAME4	329	Sat	Sun	2023-02-06	2023-02-08	OK
14			NAME5	330	Mon	Tue	2023-02-15	2023-02-17	OK
15			NAME6	331	Wed	Thu	2023-02-10	2023-02-12	OK
16
17			Feb-23						TRUE
18			Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb	Feb
19			Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue
20		EMP.#	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
21	NAME4	329	W	W	W	OFF	OFF	V	V	V	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF	W	W
22	NAME5	330	W	W	W	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF	V	V	V	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF
23	NAME6	331	OFF	OFF	W	W	W	W	W	OFF	OFF	V	V	V	W	W	OFF	OFF	W	W	W	W	W	OFF	OFF	W	W	W	W	W
24
Excel Newbie2020

Cell Formulas
Range	Formula
I17	I17	=I20<=H13
C19:AD19	C19	=C20
C21:AD23	C21	=IF(SUM((--($A21=$C$13:$C$15))*(--(C$20>=$G$13:$G$15))*(--(C$20<=$H$13:$H$15)))>0,"V", IF(SUM((--($A21=$C$13:$C$15))*((--(WEEKDAY(C$20)=WEEKDAY($E$13:$E$15)))+(--(WEEKDAY(C$20)=WEEKDAY($F$13:$F$15)))))>0,"OFF","W"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C21:AD23	Expression	=C21=C5	text	NO

 

[ExcelNewbie2020]

The columns AH and AI are shown as a date in the XL2BB minisheet; for example AH4= 44968 = Feb 11. Is it a date or a String?
So it is not mandatory that each Employee has an entry in Sheet2, right?

Maybe because i copy it from the dates but AH and AI are supposedly days..yes not mandatory

 

[awoohaw]

Maybe because i copy it from the dates but AH and AI are supposedly days..yes not mandatory

@ExcelNewbie2020 , I used a date value for all of my work in the suggestion I offered, the dates are just formated to display either month, weekday name, or day.

 

[Anthony47]

My formula is a bit more complex than awoohaw's ....
It assumes that Off days are strings (those shown in col AH & AI), whereas data in Row 3 are "dates" and their format doesn't modify the calculated outputs.
It also assumes that if the same EMP# has more that 1 entry in the VACATION table, then the Off days are the same on all the entries (see the case for EMP# 3299, that I simulated have 2 vacation periods in February)
I understood that vacation periods that are not confermed by the Status=Ok are to be ignored

With reference to the XL2BB minisheets shown below, I calculated the outputs in the green table, so that its output can be compared with the original one
For the EMP# that are in Bold in column B, I modified their Vacation table for testing.
All that said, I used in Sheet1!C17 the formula

=LET(Tableee,Sheet2!$B$4:$H$200,EmplNum,$B5,myDate,C$4,myTb,FILTER(Tableee,INDEX(Tableee,0,2)=EmplNum),myTbl,IF(ISERROR(ROWS(myTb)),SEQUENCE(,5,99999,0),myTb),sorTbl,SORT(myTbl,5),myMatch,MATCH(myDate,INDEX(sorTbl,0,5)),mayBe,IF(UPPER(TEXT(myDate,"ddd"))=UPPER(INDEX(myTbl,1,3)),"Off",IF(UPPER(TEXT(myDate,"ddd"))=UPPER(INDEX(myTbl,1,4)),"Off",  IF(ISERROR(myMatch),"W",IF(myDate<=(INDEX(myTbl,myMatch,6)*(INDEX(myTbl,myMatch,7)="OK")),"V","W")))),mayBe)

Then copy it to the right and down
Beware: XL2BB formula is wrong (it shows a different formatting strings because my Excel doesn't use English)

To adapt it to you data layout you have to work on the following items:

Tableee,Sheet2!$B$4:$H$200             This is the position of Vacation table; leave enough rows for the table to grow
EmplNum,$B5                            This is the EMP# to work with
myDate,C$4                             This is the date

The absolute/relative addressing I used let copy the formula while keeping the right references


EDIT: I cannot insert the XL2BB minisheet for Sheet1, because it makes the message longer that 100000 charachters and that is forbidden; so please rfer to the Image

MULTI_C30325.xlsm
	A	B	C	D	E	F	G	H
1
2				VACATION (VL)
3			EMP.#	OFF		START	END	STATUS
4		NAME4	329	Sab	Dom	06/02/2023	08/02/2023	ok
5		NAME5	330	Lun	Mar	15/02/2023	17/02/2023	OK
6		NAME6	331	Mer	Gio	10/02/2023	12/02/2023	OK
7		NAME4	3299	Sab	Dom	02/02/2023	08/02/2023	OK
8		NAME5	3309	Lun	Mar	15/02/2023	17/02/2023	OK
9		NAME6	339	Mer	Gio	10/02/2023	12/02/2023	OK
10		NameXX	327	Gio
11		NameXX	328			02/02/2023	04/02/2023
12		NAME4	3299	Sab	Dom	25/02/2023	03/03/2023	OK
13
14
15
Sheet2

 

[Anthony47]

...and the image doesn't show in the message above...
Let me re-try here

PS: I had to use an external image sharing service, strange...

 

[ExcelNewbie2020]

@ExcelNewbie2020 , I used a date value for all of my work in the suggestion I offered, the dates are just formated to display either month, weekday name, or day.

thanks man, but there is 1 condition missing..for the Vacation, the status should be "OK", sometimes there is empty cell or "NOT OK" under the status column..