## Highlight duplicates with same date, week or month

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:

### 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.

### Get Excel *.xlsx file

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

### Highlight duplicates on same week

Conditional formatting formula:

### Highlight duplicates on same month

Conditional formatting formula:

### Get 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

This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

### 4 Responses to “Highlight duplicates with same date, week or month”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

HI

I need to learn excel both the basic and the advance level specifically through online which includes both audio and video.

Im working in a Corporate and feel excel will enhance my skills.

Can you contact me through my emial and let me know the feasibility and convenience.

Thanks

Nirmala

Nirmala,

I have sent you an email.

/Oscar

Hi,

I'm trying to find a way to highlight cells with text/number (column B) if reoccurred 3 times or more within the last 3 days (dates in column A)

I hope someone can help

Hello,

I'm doing this for a spreadsheet where I don't want a blank cell (in the second column) to match with another blank cell. How can I modify the formula accordingly? For instance, in my work I drag down the dates in column A before entering the rest of the data. I'm looking for a match between column A&E between rows. But at the moment it does this, but also highlights all the prepped rows at the end where I've dragged down the date. I want it not to consider the same date in A and a blank in E to be a match with another same date A and blank E.

Thanks!