Conditional Formatting for when the month/year date for the same ID on two worksheets doesn't match

[slam]

On a worksheet named Date Issues, I have a list of ID's in column B, and a date in column AF in the format DD-MMM-YYYY. This date could be any day of the month.

On a worksheet named Financial Issues, I have the same list of ID's in column A. In column D on this worksheet, I have dates in the same DD-MMM-YYY format, but this will always be the 1st of the month.

I need conditional formatting in this column D to identify where the month and year do not match the month and year from the Date Issues worksheet for the same ID.

Is this possible? Could I please trouble someone for the conditional formatting formula for this?

Both worksheets have column headers, and this is for Excel 365.

Thanks!

 

[etaf]

i have used a different cell to AF - just to get all on the screen

=NOT(EOMONTH(INDEX('Date Issues'!$AF$2:$AF$28,MATCH('Date Issues'!B2,'Date Issues'!$B$2:$B$28,0)),0)=EOMONTH(D2,0))

I'm sure there would be a way to use countif based on 1st of month - but this is what i came up with

finacial sheet

Book1
	A	B	C	D	E	F
1	ID			Date 1st
2	1			4/1/24		FALSE
3	2			7/1/24		TRUE
4	3			8/1/24		FALSE
5	4			12/1/24		TRUE
6
7
8
9
10
11
12
13
14
15
Fiancial

Cell Formulas
Range	Formula
F2:F5	F2	=NOT(EOMONTH(INDEX('Date Issues'!$AF$2:$AF$28,MATCH('Date Issues'!B2,'Date Issues'!$B$2:$B$28,0)),0)=EOMONTH(D2,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D33	Expression	=NOT(EOMONTH(INDEX('Date Issues'!$AF$2:$AF$28,MATCH('Date Issues'!B2,'Date Issues'!$B$2:$B$28,0)),0)=EOMONTH(D2,0))	text	NO

date issues

Book1
	A	B	C																												AE	AF	AG	AH
1		ID																														Date
2		1																														4/23/24
3		2																														6/19/24
4		3																														8/11/24
5		4																														4/23/24
6
7
Date Issues

Dropbox

www.dropbox.com

 

[slam]

Thank you so much!

This is working perfectly until I do a sort on the data. Do I need to make something else absolute?

=IFERROR(NOT(EOMONTH(INDEX('Date Issues'!$AF$2:$AF$200,MATCH('Date Issues'!B2,'Date Issues'!$B$2:$B$200,0)),0)=EOMONTH(D2,0)),FALSE)

 

[slam]

Just noticed an issue, which is it looks like it is giving a few false positives. I can't really see a pattern or reason why. Currently I have 112 rows of data, and only 6 are wrong. You can see below that the month and year matches between the two worksheets, but these are all highlighted via the conditional formatting, which they should not be. The other 106 are correctly highlighted/not highlighted.

Financials | Date Issues

01-Jan-2023 08-Jan-2023
01-Apr-2024 01-Apr-2024
01-Jan-2024 02-Jan-2024
01-Jun-2023 30-Jun-2023
01-Sep-2023 07-Sep-2023
01-Sep-2024 02-Sep-2024

 

[etaf]

sorting should not make any difference as its using an exact match
are all the dates REAL dates on not text by any chance , maybe gives an error

if you select the entire columns of dates and change the format to general
so they all change to a number

for example those dates are

Book1
	A	B
1	44927	44934
2	45383	45383
3	45292	45293
4	45078	45107
5	45170	45176
6	45536	45537
Sheet1

 

[slam]

Yes, they're all real dates. Already converted their format with ASAP utilities to ensure this, and at your recommendation changed the format to General, and they do indeed all change to numbers like those on both worksheets.

I see that when using the formula in an adjacent column, rather than as conditional formatting, when I sort, I see the formula changes per below. Is this correct? The data on Financials and Date Issues can be different data, and in a different order.

=NOT(EOMONTH(INDEX('Date Issues'!$AF$2:$AF$300,MATCH('Date Issues'!B2,'Date Issues'!$B$2:$B$300,0)),0)=EOMONTH(D2,0))

to

=NOT(EOMONTH(INDEX('Date Issues'!$AF$2:$AF$300,MATCH('Date Issues'!B79,'Date Issues'!$B$2:$B$300,0)),0)=EOMONTH(D2,0))

 

[slam]

Ah, did I figure it out? Was one reference pointing to Date Issues when it should have been pointing to Financials? (A2 below)

=NOT(EOMONTH(INDEX('Date Issues'!$AF$2:$AF$300,MATCH(A2,'Date Issues'!$B$2:$B$300,0)),0)=EOMONTH(D2,0))

 

[etaf]

glad you sorted it out