Tính thời gian xử lý

[huongtt.tbi07]

Nhờ các anh chị giúp đỡ em viết công thức tự động tính thời gian xử lý với ạ.
Ví dụ: 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ừ 8h đến 17h30, nghỉ giữa giờ là từ 12:00 đến 13:30 (khoảng nghỉ ko tính vào thời gian làm việc)
Với vd trên, tính thời gian đúng = Tổng các khoảng sau:
Ngày 05/09: (12:00 - 8:35) + (17:30 - 13:30)
Ngày 06/09: 8:00
Ngày 07:09: (12:00-8:00) + (14:45 - 13:30)
Cả nhà giúp em với ạ

 

[befaint]

Ngày 05/09: (12:00 - 8:35) + (17:30 - 12:00)

?

 

[huongtt.tbi07]

?

E nhầm ạ. Phải là 17:30 - 13:30 ạ

 

[Phan Thế Hiệp]

Nhờ các anh chị giúp đỡ em viết công thức tự động tính thời gian xử lý với ạ.
Ví dụ: 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ừ 8h đến 17h30, nghỉ giữa giờ là từ 12:00 đến 13:30 (khoảng nghỉ ko tính vào thời gian làm việc)
Với vd trên, tính thời gian đúng = Tổng các khoảng sau:
Ngày 05/09: (12:00 - 8:35) + (17:30 - 13:30)
Ngày 06/09: 8:00
Ngày 07:09: (12:00-8:00) + (14:45 - 13:30)
Cả nhà giúp em với ạ

Tham khảo:
https://www.giaiphapexcel.com/diend...-tính-giờ-làm-việc-với-ah.136134/#post-868068

Thân

 

[huongtt.tbi07]

Tham khảo:
https://www.giaiphapexcel.com/diend...-tính-giờ-làm-việc-với-ah.136134/#post-868068

Thân

Em cảm ơn ạ. Cả chiều ngồi tìm mãi không ra bài nào tương tự.
Để mai e ngâm cứu và viết theo. Phần nào chưa biết sẽ hỏi tiếp ạ!

 

[huongtt.tbi07]

Tham khảo:
https://www.giaiphapexcel.com/diend...-tính-giờ-làm-việc-với-ah.136134/#post-868068

Thân

=NETWORKDAYS(B5,C5)*8-((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,9.5))*(WEEKDAY(B5,2)<6)+(IF(MOD(C5,1)*24<=11.5,15.5,17.5)-MEDIAN(IF(MOD(C5,1)*24<=11.5,{7.5,11.5},{13.5,17.5}),MOD(C5,1)*24))*(WEEKDAY(C5,2)<6))
Áp dụng bài kia với thời gian làm việc bắt đầu từ 7h30 đến 11h30 và từ 13h30 đến 17h30, a có thể giải thích giúp em đoạn bôi đậm, giá trị 7.5; 9.5; 15.5; 17.5 là sao không ạ?

 

[huongtt.tbi07]

Tham khảo:
https://www.giaiphapexcel.com/diend...-tính-giờ-làm-việc-với-ah.136134/#post-868068

Thân

=NETWORKDAYS(B5,C5)*8-((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,9.5))*(WEEKDAY(B5,2)<6)+(IF(MOD(C5,1)*24<=11.5,15.5,17.5)-MEDIAN(IF(MOD(C5,1)*24<=11.5,{7.5,11.5},{13.5,17.5}),MOD(C5,1)*24))*(WEEKDAY(C5,2)<6))
Áp dụng bài kia với thời gian làm việc bắt đầu từ 7h30 đến 11h30 và từ 13h30 đến 17h30, bác có thể giải thích giúp em đoạn bôi đậm, giá trị 7.5; 9.5; 15.5; 17.5 là sao không ạ?
Bác giúp e đoạn này với ạ

 

[Phan Thế Hiệp]

=NETWORKDAYS(B5,C5)*8-((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,9.5))*(WEEKDAY(B5,2)<6)+(IF(MOD(C5,1)*24<=11.5,15.5,17.5)-MEDIAN(IF(MOD(C5,1)*24<=11.5,{7.5,11.5},{13.5,17.5}),MOD(C5,1)*24))*(WEEKDAY(C5,2)<6))
Áp dụng bài kia với thời gian làm việc bắt đầu từ 7h30 đến 11h30 và từ 13h30 đến 17h30, a có thể giải thích giúp em đoạn bôi đậm, giá trị 7.5; 9.5; 15.5; 17.5 là sao không ạ?

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:

​

​

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)

1. "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
2. "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)
3. "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.

 

[huongtt.tbi07]

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:

View attachment 203648​

View attachment 203649​

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)

1. "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
2. "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)
3. "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.

Quá tâm huyết và đầy đủ ạ. Cảm ơn bác rất nhiều ạ.
Em viết lên đây không nghĩ có người vào đọc và hỗ trợ. Không ngờ lại được trả lời quá đầy đủ!!!

 

[Phan Thế Hiệp]

Quá tâm huyết và đầy đủ ạ. Cảm ơn bác rất nhiều ạ.
Em viết lên đây không nghĩ có người vào đọc và hỗ trợ. Không ngờ lại được trả lời quá đầy đủ!!!

Không có chi!

Tôi viết lên diễn đàn, giải thích rõ ràng và chi tiết với mục đích: không những hỗ trợ cho riêng bạn, mà còn cho các thành viên khác, những ai đang gặp phải khó khăn trong công tác tính công, và đang tìm vấn đề giống như bạn.

Chúc bạn học tập vui với GPE.

 

[Shee]

Tương tự giải pháp này, em cần tính thời gian thực hiện công việc với một ngày làm việc bắt đầu từ 07:30 - 17:30, thời gian nghỉ trưa 11:30 - 12:30 và trừ đi 14.5 tiếng buổi tối thì tính ntn ạ ?

 

[Phan Thế Hiệp]

Tương tự giải pháp này, em cần tính thời gian thực hiện công việc với một ngày làm việc bắt đầu từ 07:30 - 17:30, thời gian nghỉ trưa 11:30 - 12:30 và trừ đi 14.5 tiếng buổi tối thì tính ntn ạ ?

Từ 7:30 đến 17:30 là 10 tiếng, thêm 14.5 tiếng buổi tối thành ra 1 ngày có 24.5 tiếng hử bạn!?

Thân

 

[Shee]

Từ 7:30 đến 17:30 là 10 tiếng, thêm 14.5 tiếng buổi tối thành ra 1 ngày có 24.5 tiếng hử bạn!?

Thân

À xin lỗi anh, em có một tí nhầm lẫn, chính xác là 14 tiếng buổi tối ạ

 

[Phan Thế Hiệp]

À xin lỗi anh, em có một tí nhầm lẫn, chính xác là 14 tiếng buổi tối ạ

Công thức dưới đây tính giờ công từ Thứ 2 đến Thứ 6:

• Từ 7:30 đến 11:30 (buổi sáng)= 4 tiếng, và
• Từ 12:30 đến 17:30 (buổi chiều)= 5 tiếng.
• Từ 11:30 đến 12:30 nghỉ trưa không tính công.
• Tổng cộng giờ làm việc là 9 tiếng!!! (Có đúng luật hông dzậy!?)
• Cũng không cần quan tâm đến 14 tiếng còn lại trong ngày.

=NETWORKDAYS(B5,C5)*9-((MEDIAN(IF(MOD(B5,1)*24<=11.5,{7.5,11.5},{12.5,17.5}),MOD(B5,1)*24)-IF(MOD(B5,1)*24<=12,7.5,8.5))*(WEEKDAY(B5,2)<6)+(IF(MOD(C5,1)*24<=11.5,16.5,17.5)-MEDIAN(IF(MOD(C5,1)*24<=11.5,{7.5,11.5},{12.5,17.5}),MOD(C5,1)*24))*(WEEKDAY(C5,2)<6))

Enter.

Một cách khác hay hơn, gọn hơn, nhưng sẽ khó hiểu cho bạn hơn. Tôi giới thiệu công thức của anh @excel_lv1.5 đăng bài trong chủ đề: 'Tính thời gian thực hiện giữa 2 ngày'

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

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. Thank cả nhà nhiều.

www.giaiphapexcel.com

Cụ thể cho bài của bạn:

=SUM(MMULT(TEXT(MOD(B5:C5,1)*(WEEKDAY(B5:C5,2)<6)*24-{7.5;11.5;12.5},"[<0]\0;[<"&{4;0;5}&"];\"&{4;0;5})*1,{-1;1}))+(NETWORKDAYS.INTL(B5,C5,1)-(WEEKDAY(C5,2)<6))*9

Enter.

Thân