Tính toán thời gian chồng chéo trong Excel

Xin chào,

Tôi đang gặp khó khăn với công thức Excel. Tôi đang cố gắng tính toán "Thời gian ngừng hoạt động mới" dựa trên thời gian bắt đầu và kết thúc. Tôi cần xác định thời gian thực tế, loại trừ thời gian chồng chéo và ngoài giờ làm việc. Trong ví dụ bên dưới, giờ làm việc là từ 9:00 đến 17:00, tức là 480 phút. Dữ liệu tôi trích xuất từ ​​hệ thống là từ Cột A đến Cột D. Có công thức nào tính toán Thời gian ngừng hoạt động mới mà không bao gồm thời gian chồng chéo không? Hiện tại tôi đang làm thủ công trong cột hiệu chỉnh.

THỜI GIAN BẮT ĐẦU	THỜI KỲ CUỐI CÙNG	MẤT ĐIỆN	Giờ làm việc	Sửa lỗi	Sự cố mất điện mới
28/04/2015 11:44:00	28/04/2015 11:45:00	1	480	0	1
28/04/2015 12:33:00	28/04/2015 12:33:00	0	480	0	0
28/04/2015 13:10:00	28/04/2015 13:10:00	0	480	0	0
28/04/2015 13:19:00	28/04/2015 13:20:00	1	480	0	1
28/04/2015 16:04:00	28/04/2015 16:30:00	26	480	0	26
28/04/2015 03:00:00	28/04/2015 03:07:00	7	480	7	0
28/04/2015 03:05:00	28/04/2015 03:07:00	2	480	2	0
28/04/2015 12:21:00	28/04/2015 12:21:00	0	480	0	0
28/04/2015 13:56:00	28/04/2015 13:56:00	0	480	0	0
28/04/2015 16:50:00	28/04/2015 16:50:00	0	480	0	0
28/04/2015 18:19:00	28/04/2015 18:22:00	3	480	3	0
28/04/2015 19:34:00	28/04/2015 21:52:00	138	480	138	0
28/04/2015 19:39:00	28/04/2015 21:52:00	133	480	133	0

Giờ làm việc như sau:

480 tức là 09:00 - 17:00

420 tức là 09:00 - 16:00

1440 tức là 24 giờ (tức là từ 12:00 đến 23:59:59)

Tôi hy vọng những điều trên đã rõ ràng. Cảm ơn trước sự giúp đỡ của mọi người.

Anh

Deepak

Trả lời:

Giả sử thời gian bắt đầu và kết thúc luôn trùng nhau trong cùng một ngày, hãy nhập vào ô E2:

=MAX(0,SUMPRODUCT((ROW(INDIRECT("A"&(INT(MOD(A2,1)*1440)) & ":A" & (INT(MOD(B2,1)*1440))))<=540)*1)+SUMPRODUCT((ROW(INDIRECT("A"&(INT(MOD(A2,1)*1440)) & ":A" & (INT(MOD(B2,1)*1440))))>1020)*1)-1)

Các giá trị 540 và 1020 cần thay đổi dựa trên giờ làm việc, vì vậy chúng có thể được thay thế bằng các hàm IF()....