Match Day and Month Between two Dates (Without Year) from list of data

[mba_110]

Hi everyone,

I am trying to match day and month between two dates, as mentioned below these dates do not have years only Day and month.

for example

B C D
B3 = Start Date C3 = End Date D3= Query Date
14-Mar 15-Apr 13-Apr-1990
Format: (DD,MMM) (DD,MMM) (DD,MM,YYYY)

The date i am trying to match is full date with year value at D3 but the dates in C & D do not have year value.

I want if D3 Date's (Day and Month) fall between B & C's Day and month then it will be = E4 otherwise, "" blank

I hope its clear to understand.

 

[Peter_SSs]

Glad you got XL2BB working

I couldn't see that it included the expected result though? .. or an answer to my question about the expected result?

BTW, best not to include long wordy instructions in the XL2BB sheet as it makes the display rather big and less easy to see the relevant part(s)

 

[mba_110]

Glad you got XL2BB working

I couldn't see that it included the expected result though? .. or an answer to my question about the expected result?

BTW, best not to include long wordy instructions in the XL2BB sheet as it makes the display rather big and less easy to see the relevant part(s)

Formula is showing spill error if i drag this formula to cover all the rows for lookup, so i need to find the person name beside the Start and End dates as mentioned in the list, based on the search date. at D4 (Without Year), I want formula that should not have spill because there are two reasons one is we are going to do lot of work/calculations based on this result and below this cell there will be lot of content which cannot be change.

Excel Date Sample.xlsx
	A	B	C	D	E
3
4			Search Date	19-Apr-90
5	Sample Data
6
7
8		Start Date	End Date	Names
9		21-Mar	21-Apr	John Martin
10		22-Apr	20-May	Lisa Morison
11		21-May	21-Jun	Ahmed Ali
12		22-Jun	23-Jul	Rajesh Kumar
13		24-Jul	23-Aug	Mona Darling
14		24-Aug	23-Sep	Mohan Kumar
15		24-Sep	23-Oct	Jia Khan
16		24-Oct	22-Nov	Robert Anderson
17		23-Nov	22-Dec	Jay Roy
18		23-Dec	20-Jan	Anjali Verma
19		21-Jan	19-Feb	Muskan Mirza
20		20-Feb	20-Mar	Suraj Agarwal
21
22	Who is Available between these dates			John Martin
23
Sheet1

Cell Formulas
Range	Formula
D22:D33	D22	=LET(b,TEXT(--B9:B20,"mdd"),c,TEXT(--C9:C20,"mdd"),d,TEXT(--$D$4,"mdd"),IF(MOD(c-b,1200)=MOD(c-d,1200)+MOD(d-b,1200),D9:D20,""))
Dynamic array formulas.

 

[Tetra201]

@ mba_110: See if the following formula works for you:

=INDEX(D9:D20,MATCH(TRUE,LET(b,TEXT(--B9:B20,"mdd"),c,TEXT(--C9:C20,"mdd"),d,TEXT(--$D$4,"mdd"),MOD(c-b,1200)=MOD(c-d,1200)+MOD(d-b,1200)),0))

 

[Tetra201]

@ Peter_SSs: Could you please examine your MEDIAN-based formula for a Dec-Jan pair?

 

[mba_110]

@ mba_110: See if the following formula works for you:

=INDEX(D9:D20,MATCH(TRUE,LET(b,TEXT(--B9:B20,"mdd"),c,TEXT(--C9:C20,"mdd"),d,TEXT(--$D$4,"mdd"),MOD(c-b,1200)=MOD(c-d,1200)+MOD(d-b,1200)),0))

Thank you very much, formula is working perfect, people like you is always a sign of survival for others.

 

[Tetra201]

Glad to have helped.

 

[Peter_SSs]

@ Peter_SSs: Could you please examine your MEDIAN-based formula for a Dec-Jan pair?

Good point! I hadn't considered/tested that one.

However, it looks like I was on the wrong track anyway. Despite being repeatedly told that columns B & C do not have a year, and me specifically asking in post#4 if they were text values (no response), it appears from the XL2BB sample data in post #12 that columns B & C do have years (just formatted to hide them)

Given the changed circumstances, the MEDIAN idea could still be used as in column F below & I have used that column to retrieve the relevant name.

23 07 20.xlsm
	B	C	D	E	F
4		Search Date	17-Jan-90
5
6
7
8	Start Date	End Date	Names
9	21-Mar	21-Apr	John Martin		No
10	22-Apr	20-May	Lisa Morison		No
11	21-May	21-Jun	Ahmed Ali		No
12	22-Jun	23-Jul	Rajesh Kumar		No
13	24-Jul	23-Aug	Mona Darling		No
14	24-Aug	23-Sep	Mohan Kumar		No
15	24-Sep	23-Oct	Jia Khan		No
16	24-Oct	22-Nov	Robert Anderson		No
17	23-Nov	22-Dec	Jay Roy		No
18	23-Dec	20-Jan	Anjali Verma		Yes
19	21-Jan	19-Feb	Muskan Mirza		No
20	20-Feb	20-Mar	Suraj Agarwal		No
21
22			Anjali Verma
Dates2

Cell Formulas
Range	Formula
F9:F20	F9	=LET(d,--(TEXT(D$4,"dmmm")&YEAR(B$9)),IF(d=MEDIAN(d,EDATE(B9,-12*(MONTH(C9)=1)),C9),"Yes","No"))
D22	D22	=XLOOKUP("Yes",F9:F20,D9:D20)