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

### Cf duplicates category

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

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

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

Table of Contents How to highlight duplicate values Highlight the smallest duplicate number 1. How to highlight duplicate values The […]

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

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

Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in […]

Question: I need to delete duplicates in two different columns together. How do I highlight the second or more duplicates […]

### Conditional formatting category

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

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

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

Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]

This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]

The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]

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

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

Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]

Question: I have a list that I keep adding rows to. How do i create a border that expands as […]

The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]

In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]

In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]

The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]

This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]

In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]

The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]

### Dates category

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 […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

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

This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

This article explains how to find the smallest and largest value using two conditions. In this case they are date […]

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]

This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Conditional Formatting categories

## Excel categories

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