Tính khoảng thời gian làm việc (1 người xem)

[excelbucminh]

Xin chào các anh/chị trong diễn đàn, em muốn tính khoảng thời gian làm việc trong giờ hành chính (bắt đầu từ 8h30, kết thúc vào 5h30, trừ 1 tiếng nghỉ trưa), không tính cả T7 và CN. Ví dụ:
Start : 30/10/2015 4:47:00 PM
Done: 02/11/2015 2:24:00 PM
Thì kết quả thời gian làm việc là: 1h32'
Em thực sự không biết tính kiểu gì, mong các anh chị giúp đỡ
File dữ liệu mẫu e tải ở dưới. Em cảm ơn ạ

 

[bebo021999]

Tính sao ra 1h32' nhỉ?
Riêng ngày 2/11 (thứ 2) từ 8:30 đến 14:24 đã là gần 5h rồi

 

[bebo021999]

Ờ mà nghỉ trưa từ mí h tới mí h?

 

[excelbucminh]

Haizz, em muốn đập đầu vô tường.
End:02/11/2015 9:19:00 AM đấy ạ.

Ờ mà nghỉ trưa từ mí h tới mí h?

từ 12h đến 13h

 

[excelbucminh]

Tính sao ra 1h32' nhỉ?
Riêng ngày 2/11 (thứ 2) từ 8:30 đến 14:24 đã là gần 5h rồi

E viết nhầm thời gian done: 2/11/2015 9:19:00 PM

 

[excelbucminh]

Chỗ đỏ đỏ ý...là sao??? Kết quả sao là 1h32' (1 giờ 32 phút) hả???

Vâng ạ, thật ra phần này e không yêu cầu format kết quả. Em chỉ muốn ra được khoảng thời gian làm việc, con số cũng được ạ. T.T Em gà excel lắm

 

[bebo021999]

Bài này dùng công thức có vẻ khó xơi đây.
Mình mò mãi cũng ra, tuy nhiên củ chuối quá. Các sư huynh nhào zô xem có cách nào ngắn gọn hơn không.
Mình xử như sau:
TG bắt đầu: C5 = 02/11/2015 14:24
TG kết thúc: D5 = 05/11/2015 15:02
1) Tạo một dãy số liên tục, mỗi số hiển thị 1 giờ, bắt đầu từ 02/11/2015 01:00 cho đến 12/11/2015 00:00 (10 ngày), sau đó so sánh với C5 và D5, trả về 1 nếu thỏa, 0 nếu không thỏa.
HourList=(INT(C5)+ROW($2:$240)/24>=C5)*(INT(C5)+ROW($2:$240)/24<=D5)
2) Xét dãy giờ nói trên so sánh với giờ làm việc HC, trả về 1/0
WorkHour=IF(MOD(INT(C5)+ROW($2:$240)/24,1)<(8.5/24),0,IF(MOD(INT(C5)+ROW($2:$240)/24,1)<=(12/24),1,IF(MOD(INT(C5)+ROW($2:$240)/24,1)<(13/24),0,IF(MOD(INT(C5)+ROW($2:$240)/24,1)<=(17.5/24),1,0))))
3) Xét dãy giờ nói trên nếu thuộc T7, CN trả về FALSE, ngày thường trả về TRUE
Weekday=WEEKDAY(INT(C5)+ROW($2:$240)/24)<6
4) Nhân 3 dãy số với nhau ra một dãy số 0/1 thỏa 3 điều kiện trên, sau đó cộng lại, đổi ra giờ:
=SUMPRODUCT(HourList*WorkHour*Weekday)/24
5) Thêm vào yếu tố chênh lệch do số phút tạo ra:

=SUMPRODUCT(HourList*WorkHour*Weekday)/24-((WEEKDAY(C5)<6)*MINUTE(C5)-(WEEKDAY(D5)<6)*MINUTE(D5))/1440

Kết thúc bằng Ctrl-shift-enter. Mình tính tạm cho 10 ngày là tối đa, nếu thực tế nhiều hơn thì điều chỉnh trong name HourList.

 

[excelbucminh]

Bài này dùng công thức có vẻ khó xơi đây.
Mình mò mãi cũng ra, tuy nhiên củ chuối quá. Các sư huynh nhào zô xem có cách nào ngắn gọn hơn không.
Mình xử như sau:
TG bắt đầu: C5 = 02/11/2015 14:24
TG kết thúc: D5 = 05/11/2015 15:02
1) Tạo một dãy số liên tục, mỗi số hiển thị 1 giờ, bắt đầu từ 02/11/2015 01:00 cho đến 12/11/2015 00:00 (10 ngày), sau đó so sánh với C5 và D5, trả về 1 nếu thỏa, 0 nếu không thỏa.
HourList=(INT(C5)+ROW($2:$240)/24>=C5)*(INT(C5)+ROW($2:$240)/24<=D5)
2) Xét dãy giờ nói trên so sánh với giờ làm việc HC, trả về 1/0
WorkHour=IF(MOD(INT(C5)+ROW($2:$240)/24,1)<(8.5/24),0,IF(MOD(INT(C5)+ROW($2:$240)/24,1)<=(12/24),1,IF(MOD(INT(C5)+ROW($2:$240)/24,1)<(13/24),0,IF(MOD(INT(C5)+ROW($2:$240)/24,1)<=(17.5/24),1,0))))
3) Xét dãy giờ nói trên nếu thuộc T7, CN trả về FALSE, ngày thường trả về TRUE
Weekday=WEEKDAY(INT(C5)+ROW($2:$240)/24)<6
4) Nhân 3 dãy số với nhau ra một dãy số 0/1 thỏa 3 điều kiện trên, sau đó cộng lại, đổi ra giờ:
=SUMPRODUCT(HourList*WorkHour*Weekday)/24
5) Thêm vào yếu tố chênh lệch do số phút tạo ra:

=SUMPRODUCT(HourList*WorkHour*Weekday)/24-((WEEKDAY(C5)<6)*MINUTE(C5)-(WEEKDAY(D5)<6)*MINUTE(D5))/1440

Kết thúc bằng Ctrl-shift-enter. Mình tính tạm cho 10 ngày là tối đa, nếu thực tế nhiều hơn thì điều chỉnh trong name HourList.

@bebo01999: Em cảm ơn ạ
Với trình của e thì..... . Có cách nào dễ hiểu hơn ko ạ? Bài toán e đặt ra hơi khoai thì phải ạ

 

[bebo021999]

@bebo01999: Em cảm ơn ạ
Với trình của e thì..... . Có cách nào dễ hiểu hơn ko ạ? Bài toán e đặt ra hơi khoai thì phải ạ

Đương nhiên là nếu bạn tiếp xúc excel chưa nhiều thì lập đáp ứng yêu cầu bài này là bất khả thi.
Vấn đề là ra kết quả có đúng không? Có cần sửa chữa gì không?

 

[excelbucminh]

Em có 1 thắc mắc là: kết quả ở hàng C12-C14 hình như không đúng lắm. Kết quả ở ở mấy hàng trên cũng thế ạ (trừ 2 hàng đầu). T.T

 

[doveandrose]

mình tính số giờ thế này

=TIME(17,30,0) - MOD(C5,1) -(MOD(C5,1) < TIME(12,0,0))*TIME(1,0,0)+MOD(D5,1) - TIME(8,30,0) - (MOD(D5,1)>TIME(12,0,0))*TIME(1,0,0)+(NETWORKDAYS(C5,D5)-2)*TIME(8,0,0)

sẽ ra 1 con số , từ con số này chuyển sang Format xhy' chắc phải thêm 1 công đoạn nữa mà mình chưa nghĩ ra
thí dụ nếu ra 1 nghĩa là 24 tiếng tròn

 

[excelbucminh]

mình tính số giờ thế này

=TIME(17,30,0) - MOD(C5,1) -(MOD(C5,1) < TIME(12,0,0))*TIME(1,0,0)+MOD(D5,1) - TIME(8,30,0) - (MOD(D5,1)>TIME(12,0,0))*TIME(1,0,0)+(NETWORKDAYS(C5,D5)-2)*TIME(8,0,0)

sẽ ra 1 con số , từ con số này chuyển sang Format xhy' chắc phải thêm 1 công đoạn nữa mà mình chưa nghĩ ra
thí dụ nếu ra 1 nghĩa là 24 tiếng tròn

Em cảm ơn ạ, ra được con số này là e mừng r

 

[doveandrose]

Em cảm ơn ạ, ra được con số này là e mừng r

ủa ra được số đó rồi chuyển về format xxhyy' bạn làm sao chỉ tôi với

 

[excelbucminh]

e vào format cell, chọn định dạng hh:mm. Nhưng bị lỗi khi quá 24h. Ai giúp e chuyển sang định dạng có ngày với ạ

 

[doveandrose]

e vào format cell, chọn định dạng hh:mm. Nhưng bị lỗi khi quá 24h. Ai giúp e chuyển sang định dạng có ngày với ạ

ủa mắc xương rồi à ? tôi thử coi bạn làm sao thôi . Bây giờ thử đi Format là
[h]:mm
xem sao

 

[bebo021999]

Em có 1 thắc mắc là: kết quả ở hàng C12-C14 hình như không đúng lắm. Kết quả ở ở mấy hàng trên cũng thế ạ (trừ 2 hàng đầu). T.T

Í quên, xí, vào sửa cái name weekday từ <6 thành <7 nhé

 

[excelbucminh]

Em làm được r ạ! Cảm ơn các anh chị đã giúp e tận tình :d