Conditional format with EOMONTH function

[maiwarits]

Hi guys,

I have tried to add conditional format with EOMONTH function.

But I'm not sure what I do wrong, they make the condition that I made but not on the last day of month.


In the below picture: The condition should appear on 30Apr and last day of the other month that what I expect.

 

[awoohaw]

It works for me.

WorkBook1.xlsx
	B	C	D	E	F	G
1
2
3
4		12/29/2022			FALSE
5		12/30/2022			FALSE
6		12/31/2022			TRUE
7		1/1/2023			FALSE
8		1/2/2023			FALSE
Sheet1

Cell Formulas
Range	Formula
F4:F8	F4	=C4=EOMONTH(C4,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Expression	=C1=EOMONTH(C1,0)	text	NO

 

[awoohaw]

Are your values in ROW(4) actual dates? Or just the day of the month number?

 

[awoohaw]

It works using the day formatted as just "d":

WorkBook1.xlsx
	A	B	C	D
1		12/29/2022
2
3
4			29
5			30
6			31
7			1
8			2
Sheet1

Cell Formulas
Range	Formula
C4	C4	=B1
C5:C8	C5	=C4+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	Expression	=B1=EOMONTH(B1,0)	text	NO
C:C	Expression	=C1=EOMONTH(C1,0)	text	NO

 

[Peter_SSs]

It works if you apply it correctly. It appears that when yo applied the CF rule =I4=EOMONTH(I4,0) you had the whole of row 4 selected.
Select the whole of row 4 again and Conditional Formatting -> Clear Rules -> Clear Rules from Selected Cells
Then select from I4 and as far right as you want and apply the rule again.

 

[Peter_SSs]

Are your values in ROW(4) actual dates?

It looks like it. You can see the formula in cell I4 pointing at the date in D2. So, pretty safe to assume the others values in the row are dates as well I think.

 

[maiwarits]

It works if you apply it correctly. It appears that when yo applied the CF rule =I4=EOMONTH(I4,0) you had the whole of row 4 selected.
Select the whole of row 4 again and Conditional Formatting -> Clear Rules -> Clear Rules from Selected Cells
Then select from I4 and as far right as you want and apply the rule again.

Thanks!

Because I applied the entire of the row so the condition appear wrong.

 

[maiwarits]

It works using the day formatted as just "d":

WorkBook1.xlsx
	A	B	C	D
1		12/29/2022
2
3
4			29
5			30
6			31
7			1
8			2
Sheet1

Cell Formulas
Range	Formula
C4	C4	=B1
C5:C8	C5	=C4+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	Expression	=B1=EOMONTH(B1,0)	text	NO
C:C	Expression	=C1=EOMONTH(C1,0)	text	NO

Thank for your reply and explanation.

 

[Peter_SSs]

Because I applied the entire of the row so the condition appear wrong.

Not sure if you are asking for clarification about that, but here it is.
When you apply a CF formula, the formula applies to the active cell and then adjusts for all the other cells.
When you had the whole row selected, the active cell is A4.
So the formula =I4=EOMONTH(I4,0) when applied to A4 says: "If the cell 8 cells to the right of me is the end of the month of the cell 8 cells to the right of me then format me."
When that logic gets applied across, in AD4 it still says "If the cell 8 cells to the right of me is the end of the month of the cell 8 cells to the right of me then format me."
8 cells to the right of AD4 is AL4 and AL4 (30 Apr 23) is equal to EOMONTH(30-Apr-23,0) so format me (AL4). That's why AL4 is green in your image.

 

[maiwarits]

Not sure if you are asking for clarification about that, but here it is.
When you apply a CF formula, the formula applies to the active cell and then adjusts for all the other cells.
When you had the whole row selected, the active cell is A4.
So the formula =I4=EOMONTH(I4,0) when applied to A4 says: "If the cell 8 cells to the right of me is the end of the month of the cell 8 cells to the right of me then format me."
When that logic gets applied across, in AD4 it still says "If the cell 8 cells to the right of me is the end of the month of the cell 8 cells to the right of me then format me."
8 cells to the right of AD4 is AL4 and AL4 (30 Apr 23) is equal to EOMONTH(30-Apr-23,0) so format me (AL4). That's why AL4 is green in your image.

Hi Peter!

Thank you! thanks for the kind explanation. It is really helpful.