Conditional formatting based on 2 duplicate values and dates

[cpisthedbb]

Hi,

I've got a conditional formatting problem which I'm unable to solve. I have a range of dates in a column as can be seen below. All in one column is the date and whether that date was the night or day. I'm looking on highlighting the dates with conditional formatting if the dates match the previous ignoring if it's day or night. IE I want to highlight the 12/04, 26/04 and 30/04. Is there a way this can be done?

10/04 Night
09/04 Night
13/04 Night
12/04 Day
12/04 Night
16/04 Night
13/04 Night
13/04 Day
30/04 Night
26/04 Day
26/04 Night
27/04 Day
23/04 Day
30/04 Day
30/04 Night

 

[shknbk2]

Assuming the data are in cells A1:A15, one way is to add the following conditional formatting formula (with appropriate formatting style) to cells A2:A15:

=MID(A2,1,5)=MID(A1,1,5)

It will show the formatting in all but the top cell of same-date cells. For example, in your data, it would highlight the lower cell of the dates you mentioned.

If you want to also highlight the top cell, add a second formula for cells A1:A14:

=MID(A1,1,5)=MID(A2,1,5)

 

[cpisthedbb]

Assuming the data are in cells A1:A15, one way is to add the following conditional formatting formula (with appropriate formatting style) to cells A2:A15:

=MID(A2,1,5)=MID(A1,1,5)

It will show the formatting in all but the top cell of same-date cells. For example, in your data, it would highlight the lower cell of the dates you mentioned.

If you want to also highlight the top cell, add a second formula for cells A1:A14:

=MID(A1,1,5)=MID(A2,1,5)

That's brilliant, worked a treat. Thanks for you help.

 

[Peter_SSs]

If your data has a heading row you could do it with this single rule.

Excel Workbook
	A
1	Data
2	10/04 Night
3	09/04 Night
4	13/04 Night
5	12/04 Day
6	12/04 Night
7	16/04 Night
8	13/04 Night
9	13/04 Day
10	30/04 Night
11	26/04 Day
12	26/04 Night
13	27/04 Day
14	23/04 Day
15	30/04 Day
16	30/04 Night
CF Same Date

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =FIND(LEFT(A2,5),A1&A3)	Abc

 

[cpisthedbb]

Thanks for this, both work a treat

 

[Peter_SSs]

Thanks for this, both work a treat

You're welcome.