Mahesh có thể tìm ra cách tính chênh lệch giữa hai ngày và giờ trong vài phút. Tuy nhiên, anh ấy muốn tính toán sự khác biệt theo phút, nhưng loại trừ các giờ từ 5 giờ chiều đến 8 giờ sáng cũng như loại trừ mọi thứ từ 5 giờ chiều Thứ Sáu đến 8 giờ sáng Thứ Hai. Ví dụ: nếu ngày đầu tiên là 18/02/09 6:00 chiều và ngày kết thúc là 09/02/09 9:00 sáng, kết quả chính xác sẽ là 60 phút. Mahesh tự hỏi liệu điều này có thể làm được với một công thức hay không.

Rõ ràng là một công thức để đạt được kết quả mong muốn có thể rất phức tạp. Nhiều người đăng ký đã cung cấp các giải pháp khác nhau, bao gồm một số chức năng tuyệt vời do người dùng xác định. Thay vì tập trung vào tất cả chúng, tôi nghĩ rằng tôi sẽ chuyển ngay đến công thức thanh lịch nhất (ngắn nhất) và đề xuất sử dụng nó.

Giả sử rằng ngày / giờ bắt đầu của bạn nằm trong ô A1 và ngày / giờ kết thúc ở ô B1. Với những điều này, bạn có thể sử dụng công thức sau:

=(NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")

+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"17:00"

,"08:00"),"17:00")-MEDIAN(NETWORKDAYS(A1,A1)

* MOD(A1,1),"17:00","08:00")

Đây là một công thức duy nhất; nó trả về thời gian đã trôi qua. Điều này có nghĩa là bạn sẽ cần phải định dạng ô để hiển thị thời gian đã trôi qua. Nếu bạn muốn có kết quả là một số nguyên thông thường, thì bạn nên sử dụng phiên bản này của công thức, thay vào đó:

=((NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")

+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"17:00"

,"08:00"),"17:00")-MEDIAN(NETWORKDAYS(A1,A1)

MOD(A1,1),"17:00","08:00"))1440

Sự thay đổi (nhân kết quả ban đầu với 1440) dẫn đến một số phút chứ không phải là thời gian trôi qua. Giá trị 1440 được tính bằng cách nhân 60 với 24 để có số phút trong một ngày.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (5399) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: