Highlight duplicates on same date

Conditional formatting formula:

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

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