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]

The highlighted parts do not agree so I am unsure what is actually happening. Can you clarify?

The highlighted parts do not agree so I am unsure what is actually happening. Can you clarify?

Are the "dates without year" text values only then? Or are they actual dates (numbers) just not showing the year?

What about some sample data (and expected result/explanation) with XL2BB?

Also, what do you mean by "match" here since 13 April does not (exactly) match 14 March or 15 April?

 

[mba_110]

View attachment 95444
The highlighted parts do not agree so I am unsure what is actually happening. Can you clarify?


View attachment 95445
The highlighted parts do not agree so I am unsure what is actually happening. Can you clarify?

Are the "dates without year" text values only then? Or are they actual dates (numbers) just not showing the year?

What about some sample data (and expected result/explanation) with XL2BB?

Also, what do you mean by "match" here since 13 April does not (exactly) match 14 March or 15 April?

The first Yellow highlight is content in cell B3
The Second yellow highlight is B3 cell's format
The Green highlight is the cell which have full date D3.

So, lets talk about it once again, cell B3 and C3 have only (Day and Month) NO "YEAR" Value, on the other hand D3 have all the values (Day,Month,Year). so what is common in these three cells ?

The Common is (Day,Month) Bingo! so, i want to look for Day and month of B3 (Starting Date) and C3 (End Date) with the value i have in D3 here you have to ignore the Year, if there is any occurrence fall between these dates or not.

in above example i have mentioned the value of D3 =13-4-1990, so formula should check whether B3 value to C3 have D3 cell's Day and Month falling between them if yes then result should be true/do something.

Regarding, sample data with XL2BB, i am sorry to say i am unable to attach here because it bit of confidential and has completely different language than.

 

[Peter_SSs]

The first Yellow highlight is content in cell B3
The Second yellow highlight is B3 cell's format

My point was that those two things cannot both be true
If B3 content is "14-Mar" then the format would be "DD-MMM" not "DD,MMM"
If B3 format is "DD,MMM" then B3 content would be "14,Mar" not "14-Mar"

The Green highlight is the cell which have full date D3.

Yes, yet the final green highlight says "D do not have year value", hence my comment about that.

Anyway, what I think you are saying is that B3 and C3 are text values and D3 is an actual date (numerical) as shown below. Does that look/sound correct?

23 07 18.xlsm
	B	C	D
1
2	Text	Text	Actual Date
3	14-Mar	15-Apr	13-Apr-1990
Match dates

I don't really know what you mean by

if there is any occurrence fall between these dates or not.

What is the answer to that question for that sample data and why?

Regarding, sample data with XL2BB, i am sorry to say i am unable to attach here because it bit of confidential

You are asking about dates - there is nothing confidential about dates. For any other data that is confidential, if you have to include it then alter it so that it is no longer confidential. If it is not required to answer the question then it can be hidden before you generate the XL2BB mini sheet.

 

[mba_110]

My point was that those two things cannot both be true
If B3 content is "14-Mar" then the format would be "DD-MMM" not "DD,MMM"
If B3 format is "DD,MMM" then B3 content would be "14,Mar" not "14-Mar"


Yes, yet the final green highlight says "D do not have year value", hence my comment about that.

Anyway, what I think you are saying is that B3 and C3 are text values and D3 is an actual date (numerical) as shown below. Does that look/sound correct?

23 07 18.xlsm
	B	C	D
1
2	Text	Text	Actual Date
3	14-Mar	15-Apr	13-Apr-1990
Match dates

I don't really know what you mean by

What is the answer to that question for that sample data and why?



You are asking about dates - there is nothing confidential about dates. For any other data that is confidential, if you have to include it then alter it so that it is no longer confidential. If it is not required to answer the question then it can be hidden before you generate the XL2BB mini sheet.

I have attached the image, regarding XL2BB is giving me Microsoft excel potential security reasons for micro's associates with it and disabling the micros in this add in, so its not allowing me to install or run however if even i ignore and install the XL2BB add ins, the option of mini sheet and other options are not working.

 

[Tetra201]

See if the following formula works for you:

=LET(b,TEXT(--B9,"mdd"),c,TEXT(--C9,"mdd"),d,TEXT(--$D$4,"mdd"),IF(MOD(c-b,1200)=MOD(c-d,1200)+MOD(d-b,1200),"Yes","No"))

 

[Peter_SSs]

regarding XL2BB is giving me Microsoft excel potential security reasons for micro's associates with it and disabling the micros in this add in, so its not allowing me to install or run

See if this helps: XL2BB Icons greyed out

Here is another formula to test.

23 07 19.xlsm
	B	C	D
4			23-Apr-90
5
6
7
8
9	21-Mar	21-Apr	No
10	22-Apr	20-May	Yes
11	21-May	21-Jun	No
12	22-Jun	23-Jul	No
Check Dates

Cell Formulas
Range	Formula
D9:D12	D9	=LET(Yr,YEAR(D$4),IF(D$4=MEDIAN(D$4,B9&Yr,C9&Yr),"Yes","No"))

 

[mba_110]

I have attached the image, regarding XL2BB is giving me Microsoft excel potential security reasons for micro's associates with it and disabling the micros in this add in, so its not allowing me to install or run however if even i ignore and install the XL2BB add ins, the option of mini sheet and other options are not working.

Your formula is working, Great ! but for single cell selection, what if i need to check from list of people, like i attached here.

 

[Peter_SSs]

Did you try my last suggestion about how to get XL2BB going?
You will get many more potential helpers if they don't have to manually type out your data to test with.

How would we know who's birthday today since the names are listed beside a date range, not a date? I also mentioned in post #2 about providing the expected result(s). That also helps clarify exactly whazt it is that you are trying to do. See if you can give us the sample data again but with XL2BB and with the expected result manually filled in.

 

[mba_110]

Yeah just added as per the instructions provided in link.

Excel Date Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4				19-Apr-90	The Date to check if it's month and day is fall between below start and end dates and not year
5	Sample Data
6	This condition is like calculating birthday of any person, concentrate on DAY and MONTH Only, if format is not correct suggest the correct method to get the desire result.
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		Today who's birthday
23
Sheet1