Công thức Excel: Ngày định dạng có điều kiện trùng lặp
Công thức Excel: Ngày định dạng có điều kiện trùng lặp

Công thức chung
=SUMPRODUCT((start_date<=end_dates)*(end_date>=start_dates))>1
Tóm lược

Để đánh dấu các ô có ngày trùng lặp, bạn có thể sử dụng định dạng có điều kiện với công thức dựa trên hàm SUMPRODUCT. Trong ví dụ được hiển thị, công thức ở phía nam E6 là:

=SUMPRODUCT(($C6<=$D$5:$D$9)*($D6>=$C$5:$C$9))>1

Đây là công thức tương tự được sử dụng để đánh dấu toàn bộ các hàng trong bảng bằng cách sử dụng quy tắc định dạng có điều kiện dựa trên công thức.

Giải trình

Hãy xem xét một chút về cách hoạt động của các ngày trùng lặp. Để một dự án trùng với ngày của các dự án khác, hai điều kiện phải đúng:

1. Ngày bắt đầu phải nhỏ hơn hoặc bằng (

2. Ngày kết thúc của dự án phải lớn hơn hoặc bằng (> =) ít nhất một ngày bắt đầu khác trong danh sách.

Nếu cả hai điều kiện này đều đúng, ngày dự án phải trùng với một dự án khác trong danh sách đó.

Hàm SUMPRODUCT hoàn hảo cho loại kiểm tra này vì nó xử lý các phép so sánh mảng một cách thanh lịch.

Để kiểm tra ngày bắt đầu dự án có đồng nhất với tất cả các ngày kết thúc, chúng tôi sử dụng biểu thức sau:

($C6<=$D$5:$D$9)

Để kiểm tra ngày kết thúc dự án có đồng nhất với tất cả các ngày kết thúc, chúng tôi sử dụng biểu thức sau:

($D6>=$C$5:$C$9)

Các mảng kết quả có giá trị TRUE FALSE được nhân với nhau bên trong SUMPRODUCT. Điều này buộc các kết quả TRUE và FALSE thành 1s và 0s một cách tự động, vì vậy công thức được giải như sau:

=SUMPRODUCT({0;1;1;1;1}*{1;1;1;0;0})>1
=SUMPRODUCT({0;1;1;0;0})>1
=TRUE

https://exceljet.net/formula/conditional-formatting-dates-overlap