Conditional Formatting: Compare current (RC) value to the previous (RC) value

[inddon]

Hello There,

I have a table (attached file for your reference) which has a 'Date' column. I would like to have a conditional formatting as follows:

1. Whenever the current RowColumn (RC) value of month and year (Mmm-YYYY) changes then the column should be filled with a color.

Could you please advise as to how this can be achieved using conditional formatting?

Thanks & regards,
Don

 

[bosco_yip]

Try,

1] D2, filled with yellow color

2] Select D3:D17 >> Conditional Formatting >> Formula rule:

=TEXT($D6,"mmm-yy")<>TEXT(LOOKUP(9^9,$D$5:$D5),"mmm-yy")

>>



Regards
Bosco

 

[Peter Bartholomew]

Much the same thing but using named formulas:
recordNumber = ROW(Table1[@Dates]) - ROW(Table1[#Headers])
currentMonth =MONTH( Table1[@Dates] )
previousMonth = MONTH( INDEX( Table1[Dates], recordNumber - 1 ) )
format?
= (currentMonth<>previousMonth) + (recordNumber=1)

If you might go from January one year to January the next then the year would have to be taken into consideration.

 

[inddon]

Much the same thing but using named formulas:
recordNumber = ROW(Table1[@Dates]) - ROW(Table1[#Headers])
currentMonth =MONTH( Table1[@Dates] )
previousMonth = MONTH( INDEX( Table1[Dates], recordNumber - 1 ) )
format?
= (currentMonth<>previousMonth) + (recordNumber=1)

If you might go from January one year to January the next then the year would have to be taken into consideration.

Thank you Peter

 

[inddon]

Try,

1] D2, filled with yellow color

2] Select D3:D17 >> Conditional Formatting >> Formula rule:

=TEXT($D6,"mmm-yy")<>TEXT(LOOKUP(9^9,$D$5:$D5),"mmm-yy")

>>

View attachment 53172

Regards
Bosco

Thank you Bosco