Tính thời gian thực hiện giữa 2 ngày

Chia sẻ bởi:hands

★★★★★

‹ Giúp em công thức tính tổng trong... Dò tìm nhiều điều kiện với ngày... ›

Quảng cáo

Chào cả nhà,

Cả nhà có thể giúp em thiết lập công thức cho cột "TG gián đoạn" không ah.

811

Thank cả nhà nhiều.

Yêu cầu quá hay.
Suy nghĩ 30 phút chưa ra luôn. Nghĩ được trong đầu nhưng không ráp nổi công thức.
Mình comment đây để có anh chị nào vào giải giúp thì mình được thông báo vào xem ké, hic.

Làm thử,:
ICODE]D11=ROUND(SUM((IF((MOD(C11,1)>{"11:30";"17:00"}+0)*(MOD(C11,1)<{"13:00";"7:30"}+{0;1}),MOD(C11,1),{"13:00";"7:30"}+{0;1})-IF(MOD(B11,1)>{"11:30";"17:00"}+0,MOD(B11,1),{"11:30";"17:00"}+0))*(MOD(C11,1)>{"11:30";"17:00"}+0)*(MOD(B11,1)<{"13:00";"7:30"}+{0;1})*1440)+(INT(C11)-INT(B11))*960,0)

Theo tôi thấy đây là bài toán tính thời gian làm việc, cụ thể như bài của bạn thì thời gian làm việc là:

Từ 7:30 AM đến 11:30 AM
Từ 1:00 PM đến 5:00 PM

Do vậy để tính thời gian gián đoạn, thì chỉ cần lấy thời gian tổng trừ thời gian làm việc (mà bạn ghi là "Thời gian thực hiện") là ra.
Công thức dưới không cần cột phụ để tính toán, bạn thử:

E11=(DATEDIF(B11-1,C11,"d")*8-((MEDIAN(IF(MOD(B11,1)*24<=11.5,{7.5,11.5},{13,17}),MOD(B11,1)*24)-IF(MOD(B11,1)*24<=11.5,7.5,9))+(IF(MOD(C11,1)*24<=11.5,15.5,17)-MEDIAN(IF(MOD(C11,1)*24<=11.5,{7.5,11.5},{13,17}),MOD(C11,1)*24))))*60

Enter, fill xuống.

Xem thêm giải thuật và hướng dẫn công thức tại:
www.giaiphapexcel.com/diendan/threads/t%C3%ADnh-th%E1%BB%9Di-gian-x%E1%BB%AD-l%C3%BD.137565/#post-879939

www.giaiphapexcel.com/diendan/threads/t%C3%ADnh-th%E1%BB%9Di-gian-th%E1%BB%B1c-hi%E1%BB%87n-gi%E1%BB%AFa-2-ng%C3%A0y.149931/#post-972029

Cám ơn các bạn nhiều, đúng như bài những bài test trên thì công thức sẽ cần tiếp tục hoàn thiện. Vì mình đã phát hiện công thức sai khi thời gian bắt đầu rơi vào buổi chiều, và mình đang suy luận ngược lại để tìm thuật toán đúng.Mình chưa hiểu đoạn code này sao lại có 15.5: (IF(MOD(C11,1)*24<=11.5,15.5,17). Bạn có thể giải thích cho mình hiểu rõ được không?

Xem giải thích mục '3.Thời gian không tính công lúc ra' theo link trên:

GIẢI THUẬT CHO BÀI TOÁN TÍNH GIỜ CÔNG LÀM VIỆC BẰNG CÔNG THỨC
a/ Giải thuật:
813
Như ví dụ bạn đưa tại bài #1 được tính như sau: "Thời gian bắt đầu là 05/09/2018 08:35:00, Thời gian kết thúc là 07/09/2018 14:45:20; Tính thời gian xử lý, biết rằng thời gian làm việc của 1 ngày là từ 8h00 đến 17h30, nghỉ giữa giờ là từ 12h00 đến 13h30 (khoảng nghỉ không tính vào thời gian làm việc)"

"Tổng giờ công 8h/ngày": (Từ ngày 05/09 đến 07/09) = 3 ngày x 8h = 24 tiếng.

"Thời gian không tính công lúc Vào": (08:35:00 – 08:00:00) = 00:35:00

"Thời gian không tính công lúc Ra": (17:30:00 – 14:45:20) = 02:44:40

=>"Giờ công thực hiện" = 24:00:00 – (00:35:00 + 02:44:40) = 20:44:20 quy đổi ra thập phân (x 24h) bằng = 20.67 tiếng

b/ Giải thích Công thức thực hiện: (Áp dụng cho tính thời gian làm việc từ Thứ Hai đến Thứ Sáu; Thời gian làm việc là 7:30-11:30 và 13:30-17:30)

"Tổng giờ công 8h/ngày":

Dùng hàm: NETWORKDAYS( 'Ngày Giờ Vào' , 'Ngày Giờ Ra' ) * 8 (hàm này đã loại trừ ngày T.7 và CN)

Có thể liệt kê loại trừ ra các ngày nghỉ lễ trong năm: NETWORKDAYS( 'Ngày Giờ Vào' , 'Ngày Giờ Ra' , 'Danh sách Ngày nghỉ lễ' ) * 8

[*]"Thời gian không tính công lúc Vào":

Dùng công thức: ( 'Giờ Vào' – 'Mốc Giờ Vào' )

'Giờ Vào'=MEDIAN( IF(MOD(B5,1)*24<=11.5,{7.5,11.5},{13.5,17.5}) , MOD(B5,1)*24 )

IF(MOD(B5,1)*24<=11.5,{7.5,11.5},{13.5,17.5}): Nếu Giờ Vào nhỏ hơn Mốc 11:30, thì lấy 'Mốc buổi Sáng: 7:30-11:30. Nếu Giờ Vào lớn hơn Mốc 11:30, thì lấy 'Mốc buổi Chiều: 13:30-17:30.

Hàm MEDIAN( 'Mốc buổi Sáng/Chiều' , 'Giờ Vào' ): Tìm 'Giờ' trung vị. Ví dụ: Nếu giờ vào là 6:30, thì MEDIAN({7.5,11.5}, 6.5) = 7.5 là giờ trung vị (6.5<7.5<11.5), tức nếu đi làm sơm hơn mốc quy định 7:30 thì sẽ lấy đúng mốc vào 7:30; Nếu giờ vào là 9:00, thì MEDIAN({7.5,11.5}, 9) = 9 là giờ trung vị (7.5<9<11.5). Nếu giờ vào là 12:30 (Vào giờ nghỉ trưa), thì MEDIAN({13.5,17.5}, 12.5) = 13.5 là giờ trung vị (12.5<13.5<17.5) (do giờ Vào lớn hơn 11:30, nên sẽ lấy 'Mốc buổi chiều' như trên trình bày).

[*]'Mốc Giờ Vào' = IF(MOD(B5,1)*24<=11.5,7.5,9.5)

Nếu Giờ vào nhỏ hơn Mốc 11:30, thì lấy Mốc 7:30. Nếu Giờ vào lớn hơn Mốc 11:30, thì lấy Mốc 9:30, tức nếu giờ vào sau Mốc 11:30 sẽ dịch chuyển Mốc 7:30 cộng thêm khoảng thời gian nghỉ trưa: 13:30-11:30 = 2 tiếng, tức từ 7:30 sẽ thành 9:30

[*]Loại bỏ nếu Ngày Giờ vào là Thứ Bảy và Chủ Nhật= (WEEKDAY(B5,2)<6)

[*]"Thời gian không tính công lúc Ra":

Dùng công thức: ( 'Mốc Giờ Ra' – 'Giờ Ra' )

'Mốc Giờ Ra' = (IF(MOD(C5,1)*24<=11.5,15.5,17.5)

Nếu Giờ ra nhỏ hơn Mốc 11:30, thì lấy Mốc 15:30, tức nếu giờ ra trước Mốc 11:30 sẽ dịch chuyển Mốc 17:30 trừ khoảng thời gian nghỉ trưa: 13:30-11:30 = 2 tiếng, tức từ 17:30 sẽ thành 15:30. Nếu Giờ ra lớn hơn Mốc 11:30, thì lấy Mốc 17:30.

[*]'Giờ Ra' = MEDIAN( IF(MOD(C5,1)*24<=11.5,{7.5,11.5},{13.5,17.5}) , MOD(C5,1)*24) Tương tự như cách giải thích 'Giờ Vào' ở trên nhưng tính cho Giờ Ra.
[*]Loại bỏ nếu Ngày Giờ ra là Thứ Bảy và Chủ Nhật= (WEEKDAY(C5,2)<6)

Áp dụng thực tế cho khung thời gian làm việc của Cty bạn: Giả sử tính công từ Thứ Hai đến Thứ Sáu, từ 8:00-12:00 và 13:30-17:30 như sau
=NETWORKDAYS(B5,C5)*8-((MEDIAN(IF(MOD(B5,1)*24<=12,{8,12},{13.5,17.5}),MOD(B5,1)*24)-IF(MOD(B5,1)*24<=12,8,9.5))*(WEEKDAY(B5,2)<6)+(IF(MOD(C5,1)*24<=12,16,17.5)-MEDIAN(IF(MOD(C5,1)*24<=12,{8,12},{13.5,17.5}),MOD(C5,1)*24))*(WEEKDAY(C5,2)<6))
Thân.