Công thức chung
=(NETWORKDAYS(start,end)-1)*(upper-lower) +IF(NETWORKDAYS(end,end),MEDIAN(MOD(end,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(start,start)*MOD(start,1),upper,lower)
Tóm lược
Để tính tổng số giờ làm việc giữa hai ngày và giờ, bạn có thể sử dụng công thức dựa trên hàm NETWORKDAYS. Trong ví dụ được hiển thị, E5 chứa công thức này:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),upper,lower)
trong đó "dưới" là phạm vi được đặt tên H5 và "trên" là phạm vi được đặt tên H6.
Lưu ý: ví dụ này được lấy cảm hứng từ một thử thách công thức trên Chandoo và một giải pháp hoàn chỉnh hơn được cung cấp bởi bậc thầy công thức Barry Houdini trên diễn đàn MrExcel.
Giải trình
Công thức này tính tổng số giờ làm việc giữa hai ngày và giờ, xảy ra giữa thời gian "thấp hơn" và "cao hơn". Trong ví dụ được hiển thị, thời gian thấp hơn là 9:00 sáng và thời gian trên là 5:00 chiều. Các giá trị này xuất hiện trong công thức dưới dạng các phạm vi được đặt tên là "dưới" và "trên".
Logic của công thức là tính toán tất cả các giờ làm việc có thể có giữa ngày bắt đầu và ngày kết thúc, bao gồm, sau đó lùi lại bất kỳ giờ nào trong ngày bắt đầu xảy ra giữa thời gian bắt đầu và thấp hơn và bất kỳ giờ nào vào ngày kết thúc xảy ra giữa thời gian cuối và thời gian trên.
Hàm NETWORKDAYS xử lý việc loại trừ các ngày cuối tuần và ngày lễ (khi được cung cấp dưới dạng phạm vi ngày). Bạn có thể chuyển sang NETWORKDAYS.INTL nếu lịch biểu của bạn có những ngày làm việc không chuẩn.
Định dạng đầu ra
Kết quả là một số thể hiện tổng số giờ. Giống như tất cả các lần Excel, bạn sẽ cần phải định dạng đầu ra với một định dạng số phù hợp. Trong ví dụ được hiển thị, chúng tôi đang sử dụng:
(h):mm
Các dấu ngoặc vuông ngăn không cho Excel di chuyển khi số giờ lớn hơn 24. Nói cách khác, chúng giúp có thể hiển thị số giờ lớn hơn 24. Nếu bạn cần giá trị thập phân cho hàng giờ, bạn có thể nhân kết quả với 24 và định dạng dưới dạng số thường xuyên.
Phiên bản đơn giản
Nếu thời gian bắt đầu và kết thúc luôn xảy ra giữa thời gian thấp hơn và cao hơn, bạn có thể sử dụng phiên bản đơn giản hơn của công thức này:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Không có thời gian bắt đầu và thời gian kết thúc
Để tính tổng số giờ làm việc giữa hai ngày, giả sử tất cả các ngày đều là ngày làm việc, bạn có thể sử dụng công thức đơn giản hơn:
=NETWORKDAYS(start,end,holidays)*hours
Xem giải thích ở đây để biết chi tiết.
