Author: Oscar Cronquist Article last updated on December 21, 2018

The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, only subsequent duplicates are highlighted.

Conditional formatting formula:

=SUMPRODUCT(--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3))>1

Explaining Conditional formatting formula

Step 1 - Concatenate cell values

The cell references $C3 and $D3 are locked to each column, however, they change when CF move to the next row. The ampersand sign concatenates the values in these cells.

$C3&"-"&$D3

becomes

40184&"-"&1150

and returns

"40184-1150"

Step 2 - Concatenate values using expanding cell references

These cell references expand as the CF moves to cells below, the formula keeps track of previous values so it can detect duplicate values.

$C3:$C$3&"-"&$D3:$D$3

becomes

40184&"-"&1150

and returns

"40184-1150".

Step 3 - Compare concatenated values

The equal sign lets you compare values, the result is always TRUE or FALSE.

$C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3

becomes

"40184-1150" = "40184-1150"

and returns TRUE.

Step 4 - Convert boolean values

The SUMPRODUCT function can't work with boolean values, we must convert it (them) to their numerical equivalents. TRUE = 1 and FALSE equals 0 (zero).

--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3)

becomes

--(TRUE)

and returns 1.

Step 5 - Sum values

The SUMPRODUCT function sums the values in the array in this step.

SUMPRODUCT(--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3))

becomes

SUMPRODUCT(1)

and returns 1.

Step 6 - Check if duplicate

This steps checks if the sum is larger than 1, if it is then TRUE is returned and the cell is highlighted.

SUMPRODUCT(--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3))>1

becomes

1>1

and returns FALSE. Cell C3 is not highlighted.

Download Excel *.xlsx file

Highlight-duplicates-within-same-date-week-month-year.xlsx

Highlight duplicates on same week

Conditional formatting formula:

=SUMPRODUCT(--($B16&"-"&YEAR($C16)&"-"&$D16=$B16:$B$16&"-"&YEAR($C16:$C$16)&"-"&$D16:$D$16))>1

Highlight duplicates on same month

Conditional formatting formula:

=SUMPRODUCT(--(YEAR($C29)&"-"&MONTH($C29)&"-"&$D29=YEAR($C29:$C$29)&"-"&MONTH($C29:$C$29)&"-"&$D29:$D$29))>1

Download excel sample file for this article.

Highlight-duplicates-within-same-date-week-month-year.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

YEAR(serial_number) returns the year of a date, an integer of the range 1900-9999

MONTH(serial_number) returns the month, a number from 1 (January) to 12 (December)

WEEKNUM(serial_number, return_type)
Returns the week number in the year

Related articles

How to highlight duplicate values

The picture above shows duplicate values in column B, only the second or more duplicates are colored and easily identified. [โ€ฆ]

Highlight duplicate values in a cell range

The following conditional formula highlights only the second instance or more of a value in a cell range. Conditional formatting [โ€ฆ]

Highlight more than once taken course in any given day

Question: My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any [โ€ฆ]

List all unique events in a month

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to [โ€ฆ]

Highlight duplicate records

This blog post shows you how to easily identify duplicate rows or records in a list. Conditional formatting formula: =COUNTIFS($B$3:$B$15, [โ€ฆ]

Highlight duplicate columns

This article describes how to highlight duplicate records arranged into a column each, if you are looking for records entered [โ€ฆ]