Compare Two Dates by Ignoring the Year

[gymwrecker]

Is it possible to use a formula in Conditional Formatting that compares two dates but ignores the year? Using the below data as an example, cells A3 and B3 share the same month and day, therefore, I would like to use Conditional formatting so cell B3 can turn green or any color I chose... Please advise...

A1 = 1995/05/20
B1 = 1996/05/19

A2 = 1990/11/21
B2 = 1993/10/02

A3 = 1994/05/13
B3 = 1995/05/13

 

[Micron]

Where my test range is B2:B16 and the cf applies to that range, this works to highlight the cell above. Perhaps if you offset -1 instead of 1 that will work for you.

=AND(MONTH(B2)=MONTH(OFFSET(B2,1,0)),DAY(B2)=DAY(OFFSET(B2,1,0)))

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this is what you want. It assumes the values are actual dates (numbers), not text values.

22 09 10.xlsm
	A	B
1	1995/05/20	1996/05/19
2	1990/11/21	1993/10/02
3	1994/05/13	1995/05/13
Match day month

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B3	Expression	=TEXT(A1,"ddmm")=TEXT(B1,"ddmm")	text	NO

 

[gymwrecker]

Thank you Peter! I just updated my account details. For some reason I cannot get it to work, not sure what is it that I'm doing wrong. Actual mini table is below. On this example, I need for cell H7 to change to green since the month and day is the same as cell G7:

Match-Day-Month.xlsx
	A	B	C	D	E	F	G	H
1	RCC	Rank	Years Creditable Service	Home ZIP Code	Home State	County	Initial Entry Military Date	Year End Retirement (Mo/Dy)
2	TPU	MAJ	21	29607	SC	Greenville	1980-02-03	0226
3	TPU	LTC	26	20147	VA	Loudoun	1980-05-01	0531
4	TPU	SSG	25	96793	HI	Maui	1981-02-23	0314
5	TPU	SGM	36	88007	NM	Dona Ana	1981-08-19	0818
6	TPU	COL	35	96707	HI	Honolulu	1982-02-19	1023
7	TPU	CW5	37	87111	NM	Bernalillo	1982-04-01	0401
8	TPU	LTC	40	96822	HI	Honolulu	1982-04-12	0411
SheetData

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2	Expression	=TEXT(G2,"ddmm")=TEXT(H2,"ddmm")	text	NO

 

[Peter_SSs]

For some reason I cannot get it to work,

The reason is that in your original examples, both columns were dates. In your latest sample, column H is not a date.

Depending on whether your column H is a text value (as in my column H) or is a numerical value formatted to 4 digits (as in my column I) one of these should work for you.

22 09 11.xlsm
	G	H	I
1	Initial Entry Military Date	Year End Retirement (Mo/Dy)
2	1980-02-03	0226	0226
3	1980-05-01	0531	0531
4	1981-02-23	0314	0314
5	1981-08-19	0818	0818
6	1982-02-19	1023	1023
7	1982-04-01	0401	0401
8	1982-04-12	0411	0411
Match day month

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I8	Expression	=--TEXT(G2,"mdd")=I2	text	NO
H2:H8	Expression	=TEXT(G2,"mmdd")=H2	text	NO

 

[gymwrecker]

Peter, I got it to work now! Thank you for the expert advise!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.