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]
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.;1
- 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.
Giải trình
=SUMPRODUCT[[$C5=$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 [=] đế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=$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=$C$5:$C$9]]>1
0
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