Highlight duplicates on same date, week or month using conditional formatting in excel
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
Related posts:
Filter duplicates within same date, week or month in excel
Highlight odd/even months using conditional formatting in excel 2007
Highlight duplicates using conditional formatting in excel
Highlight the second or more duplicates in two lists using conditional formatting in excel
Recommended categories
