Formula to find date and return column number

[jc352]

I have a roster of employees and the dates they have requested off. The employee name is in column A, their location is in column B and their requested time off will either be in C through E (PTO), F through J (PH) or K through P (DH) depending on the entitlement time used. For simplicity this information will be on Test. On sheet 2 i have a calendar that i would like this information displayed on. On sheet 2 the year is in A1, the month (spelled out) is in A2 and the day is in C4. I am looking for a formula that will provide the employee name, location and type of entitlement used (PTO, PH or DH) for that date. This inforation would be displayed in cells A5, A6, A7, A8 and A9.

Here is the formula i currently have, which i am not able to get to indicate the correct entitlement. It just goes with the first item found when pulled down.

Current formula:

=IFERROR(INDEX(Test!$A$3:$A$45,SMALL(IF(DATE($A$1,MONTH(DATEVALUE($A$2&"1")),$C$4)=Test!$C$3:$P$45,ROW(Test!$C$3:$P$45)-ROW(Test!$A$3)+1),ROW(1:1)))&" - "&INDEX(Test!$B$3:$B$45,SMALL(IF(DATE($A$1,MONTH(DATEVALUE($A$2&"1")),$C$4)=Test!$C$3:$P$45,ROW(Test!$C$3:$P$45)-ROW(Test!$B$3)+1),ROW(1:1)))&" "&IF(AGGREGATE(15,6,COLUMN(Test!$C$3:$P$45)/(Test!$C$3:$P$45=DATE($A$1,MONTH(DATEVALUE($A$2&1)),$C$4)),1)<4,"(PTO)",IF(AND(AGGREGATE(15,6,COLUMN(Test!$C$3:$P$45)/(Test!$C$3:$P$45=DATE($A$1,MONTH(DATEVALUE($A$2&1)),$C$4)),1)>3,AGGREGATE(15,6,COLUMN(Test!$C$3:$P$45)/(Test!$C$3:$P$45=DATE($A$1,MONTH(DATEVALUE($A$2&1)),$C$4)),1)<9),"(PH)","(DH)")),"")

I appreciate any help that can be offered.

 

[Cubist]

Rather than describe your problem. I find it's faster to get a solution by posting a sample sheet using XL2BB and updating your profile to let people know what Excel version you're using.

 

[jc352]

Sheet with infomation for formula on calendar sheet.

Employee Name	Location	PTO	PTO2	PTO3	PH	PH2	PH3	PH4	PH5	DH	DH2	DH3	DH4	DH5	DH6
Bill	OXN	7/1/2024
Jack	OXN
Sue	OXN				7/1/2024
Dave	OXN
Gary	SBA
Austin	SBA
Bobby	SBA										7/1/2024
Edgar	SBA
Sam	SBA
Hector	SBA

Calendar Sheet

2024 Vacation Calendar - Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	2024
2	July
3	Monday			Tuesday		Wednesday		Thursday		Friday		Saturday		Sunday
4			1		2		3		4		5		6		7
5	Bill - OXN (PH)
6	Sue - OXN (PH)
7	Bobby - SBA (PH)
8
9
10
Sheet 2

Cell Formulas
Range	Formula
A2	A2	=TEXT(DATE(A1,7,1),"mmmm")
C4	C4	=IF(P1=2,1,"")
E4	E4	=IF(C4<>"",C4+1,IF($P$1=3,1,""))
G4	G4	=IF(E4<>"",E4+1,IF($P$1=4,1,""))
I4	I4	=IF(G4<>"",G4+1,IF($P$1=5,1,""))
K4	K4	=IF(I4<>"",I4+1,IF($P$1=6,1,""))
M4	M4	=IF(K4<>"",K4+1,IF($P$1=7,1,""))
O4	O4	=IF(M4<>"",M4+1,IF($P$1=1,1,""))
A5:A9	A5	=IFERROR(INDEX(Test!$A$3:$A$45,SMALL(IF(DATE($A$1,MONTH(DATEVALUE($A$2&"1")),$C$4)=Test!$C$3:$P$45,ROW(Test!$C$3:$P$45)-ROW(Test!$A$3)+1),ROW(1:1)))&" - "&INDEX(Test!$B$3:$B$45,SMALL(IF(DATE($A$1,MONTH(DATEVALUE($A$2&"1")),$C$4)=Test!$C$3:$P$45,ROW(Test!$C$3:$P$45)-ROW(Test!$B$3)+1),ROW(1:1)))&" "&IF(AGGREGATE(15,6,COLUMN(Test!$C$3:$P$45)/(Test!$C$3:$P$45=DATE($A$1,MONTH(DATEVALUE($A$2&1)),$C$4)),1)<4,"(PH)",IF(AND(AGGREGATE(15,6,COLUMN(Test!$C$3:$P$45)/(Test!$C$3:$P$45=DATE($A$1,MONTH(DATEVALUE($A$2&1)),$C$4)),1)>3,AGGREGATE(15,6,COLUMN(Test!$C$3:$P$45)/(Test!$C$3:$P$45=DATE($A$1,MONTH(DATEVALUE($A$2&1)),$C$4)),1)<36),"(PTO)","(DH)")),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4,E4,G4,I4,K4,M4,O4	Cell	does not contain a blank value	text	NO