Đánh dấu các ngày chồng chéo trong Excel

Để quản lý hiệu quả các dự án hoặc hoạt động, chúng ta gặp một tình huống cần xác định dự án 1 rơi vào dự án 2 bao nhiêu ngày. Sử dụng phương pháp tương tự, chúng ta cũng có thể tính toán số ngày của phạm vi ngày dự án (ngày bắt đầu và ngày kết thúc) trùng lặp với phạm vi ngày cụ thể (ngày bắt đầu và ngày kết thúc nhất định)

Đánh dấu các ngày chồng chéo trong Excel
Hình 1. Tính toán chồng chéo ngày theo ngày

Tính chồng chéo ngày theo ngày với phạm vi ngày cụ thể

Khi chúng tôi có một danh sách các phạm vi ngày của dự án và chúng tôi muốn xem có bao nhiêu ngày của mỗi dự án nằm trong một phạm vi ngày cụ thể thì chúng tôi cần tính ngày chồng chéo theo ngày. Khi chúng ta muốn tính số ngày giữa hai ngày thì chúng ta chỉ cần trừ ngày bắt đầu cho ngày kết thúc, chẳng hạn như;

=End_Date - Start_Date +1

Ghi chú. Để bao gồm cả ngày bắt đầu và ngày kết thúc, chúng tôi bù kết quả bằng cách thêm 1

Theo cùng một quy tắc, trước tiên chúng ta cần tìm hiểu “ Ngày kết thúc ”, tùy theo ngày nào thấp hơn giữa hai ngày kết thúc, sử dụng < . Cuối cùng, chúng tôi trừ “MIN function, and then we need to find out “Start Date”, whichever is higher between two start dates, using the MAX function. Finally, we subtract the “ Ngày bắt đầu ” khỏi “ Ngày kết thúc ” in the following formula syntax;

=MAX(MIN(End1,End2)-MAX(Start1,Start2)+1,0)

Ở đây, hàm MAX bên ngoài được sử dụng để bẫy giá trị âm và thay vào đó trả về 0. Trong ví dụ của chúng tôi, để tính ngày chồng chéo theo ngày trong từng phạm vi ngày của dự án với phạm vi ngày cụ thể, chúng tôi cần áp dụng công thức sau trong ô E2 và sao chép xuống các ô khác trong cột E;

=MAX(MIN($H$3,D2)-MAX($H$2,C2)+1,0)

Đánh dấu các ngày chồng chéo trong Excel
Hình 2. Tính chồng chéo ngày theo ngày với phạm vi ngày cụ thể

Tính chồng chéo ngày theo số ngày giữa hai phạm vi ngày

Khi chúng tôi muốn biết có bao nhiêu ngày trong một phạm vi ngày rơi vào một phạm vi ngày khác, thì chúng tôi cần tính toán ngày chồng chéo theo số ngày giữa hai phạm vi ngày hoặc dự án. Sử dụng phương pháp tương tự, chúng tôi sẽ sử dụng công thức sau trong ô E3 để tìm hiểu xem Dự án 1 rơi vào Dự án 2 bao nhiêu ngày, v.v.;<

=MAX(MIN(D2,D3)-MAX(C2,C3)+1,0)

Sao chép công thức xuống các ô khác

Đánh dấu các ngày chồng chéo trong Excel
Hình 3. Tính chồng chéo ngày theo số ngày giữa hai phạm vi ngày

Kết nối tức thì với Chuyên gia thông qua Dịch vụ Excelchat của chúng tôi

Hầu hết thời gian, vấn đề bạn cần giải sẽ phức tạp hơn một ứng dụng đơn giản của công thức hoặc hàm. Nếu bạn muốn tiết kiệm hàng giờ nghiên cứu và sự thất vọng, hãy dùng thử dịch vụ Excelchat trực tiếp của chúng tôi. Các chuyên gia Excel của chúng tôi luôn sẵn sàng 24/7 để trả lời bất kỳ câu hỏi nào về Excel mà bạn có thể có. Chúng tôi đảm bảo kết nối trong vòng 30 giây và giải pháp tùy chỉnh trong vòng 20 phút.

. This is where we will insert the formula to check conditional formatting overlap and return the result as TRUE hoặc FALSE.

Đánh dấu các ngày chồng chéo trong Excel
Hình 2. Thiết lập dữ liệu

Áp dụng hàm TÓM TẮT Excel

  • Chúng tôi sẽ nhấp vào Ô E5
  • Chúng tôi sẽ chèn công thức bên dưới vào Ô E5
    =SUMPRODUCT(($C5<=$D$5:$D$9)*($D5>=$C$5:$C$9))>1
  • Chúng tôi sẽ nhấn phím enter

 

 

Đánh dấu các ngày chồng chéo trong Excel
Hình 3. Sử dụng hàm TÓM TẮT Excel

  • Chúng tôi nhấp lại vào Ô E5 và sử dụng công cụ xử lý điền (dấu cộng được tìm thấy ở đáy của Cell E5) to drag down the formula into the other cells.

Đánh dấu các ngày chồng chéo trong Excel
hinh 4. Kết quả áp dụng hàm SUMPRODUCT kiểm tra ngày tháng định dạng có điều kiện trùng nhau trong Excel

Giải trình

=SUMPRODUCT(($C5<=$D$5:$D$9)*($D5>=$C$5:$C$9))>1

Chức Hàm TỔNG KẾT CỦA Excel có thể nhân các dãy hoặc mảng với nhau để trả về tổng các tích. Trong hình minh họa ở trên, chúng tôi đã sử dụng hàm Excel SUMPRODUCT để đánh dấu các ô có ngày trùng nhau. Nó thực sự kiểm tra ngày bắt đầu và ngày kết thúc của mỗi khóa học, tương ứng với tất cả ngày bắt đầu và ngày kết thúc trong bảng excel của chúng tôi. Tuy nhiên, công thức này phụ thuộc vào hai điều kiện để trả về kết quả TRUE .

  • Ngày bắt đầu phải nhỏ hơn hoặc bằng (<=) ít nhất một ngày kết thúc khác và toàn bộ danh sách hoặc phạm vi
  • Ngày kết thúc phải lớn hơn hoặc bằng (>=) đến ít nhất một ngày khác trong dữ liệu

Công thức hoạt động theo

  • Kiểm tra Ngày bắt đầu khóa học với các Ngày kết thúc khác bằng cách sử dụng biểu thức ($C5<=$D$5:$D$9). Điều này sẽ trả về kết quả như thế này. ______7_______.
  • Tiếp theo, nó kiểm tra ngày bắt đầu khóa học so với các ngày bắt đầu khác bằng cách sử dụng biểu thức ($D5>=$C$5:$C$9). Điều này sẽ trả về kết quả như thế này. ____9_______.

Nó được giải quyết như thế này

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

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

=SUMPRODUCT(($C5<=$D$5:$D$9)*($D5>=$C$5:$C$9))>10

Kết nối tức thì với Chuyên gia thông qua Dịch vụ Excelchat của chúng tôi

Hầu hết thời gian, vấn đề bạn cần giải sẽ phức tạp hơn là một ứng dụng đơn giản của một công thức hoặc hàm. Nếu bạn muốn tiết kiệm hàng giờ nghiên cứu và thất vọng, hãy thử dịch vụ Excelchat trực tiếp của chúng tôi. Các chuyên gia Excel của chúng tôi luôn sẵn sàng 24/7 để trả lời bất kỳ câu hỏi nào về Excel mà bạn có thể có. Chúng tôi đảm bảo kết nối trong vòng 30 giây và giải pháp tùy chỉnh trong vòng 20 phút

Có cách nào để đánh dấu ngày trong Excel không?

Đánh dấu các ngày trước hôm nay bằng cách sử dụng định dạng có điều kiện tích hợp sẵn .
Chọn các ô chứa ngày mà bạn muốn áp dụng định dạng có điều kiện
Nhấp vào tab Trang chủ trong Dải băng
Nhấp vào Định dạng có điều kiện trong nhóm Kiểu. .
Chọn Đánh dấu quy tắc ô. .
Từ menu thả xuống, chọn Ngày xảy ra

Định dạng có điều kiện có thể chồng lên nhau không?

Một số ô có phạm vi định dạng có điều kiện chồng lấp . Các phiên bản Excel trước sẽ không đánh giá tất cả các quy tắc định dạng có điều kiện trên các ô chồng chéo. Các ô chồng chéo sẽ hiển thị định dạng có điều kiện khác nhau.