Xin công thức tính thời gian hoàn thành công việc

[thuhien.st]

Em chào các anh chị trong diễn đàn. Em có một bảng excel có Cột A là cột thời gian bắt đầu, Cột B là cột thời gian hoàn kết thúc. Định dạng thời gian bắt đầu và kết thúc là dạng "yyyy-mm-dd hh:mm:ss". Anh chị hướng dẫn em công thức tính thời gian hoàn thành công việc (tính theo số giờ) thỏa mãn điều kiện: Thời gian được tính giờ làm việc trong khoảng từ 8h30 đến 18h00 từ thứ 2 đến thứ 6, từ 8h30 đến 12h00 ngày thứ 7 (không cần loại trừ lễ tết, không cần loại trừ giờ nghỉ trưa)

Em cảm ơn ạ

 

[hoangtuan.net]

Mình ko hiểu câu hỏi

 

[thuhien.st]

Mình ko hiểu câu hỏi

Dạ, ví dụ em có thời gian bắt đầu là "2024-01-01 07:40:12" thời gian kết thúc là "2024-01-05 09:55:09"
Em muốn xin công thức tính thời gian hoàn thành công việc từ khi bắt đầu cho đến khi hết thúc là hết bao nhiêu giờ với điều kiện chỉ được tính giờ trong khoảng từ 8h30 đến 18h00 từ thứ 2 đến thứ 6, từ 8h30 đến 12h00 ngày thứ 7 (không cần loại trừ lễ tết, không cần loại trừ giờ nghỉ trưa) ạ

 

[Phan Thế Hiệp]

Dạ, ví dụ em có thời gian bắt đầu là "2024-01-01 07:40:12" thời gian kết thúc là "2024-01-05 09:55:09"
Em muốn xin công thức tính thời gian hoàn thành công việc từ khi bắt đầu cho đến khi hết thúc là hết bao nhiêu giờ với điều kiện chỉ được tính giờ trong khoảng từ 8h30 đến 18h00 từ thứ 2 đến thứ 6, từ 8h30 đến 12h00 ngày thứ 7 (không cần loại trừ lễ tết, không cần loại trừ giờ nghỉ trưa) ạ

Cho:
A3= "2024-01-01 07:40:12"
B3= "2024-01-05 09:55:09"

Với điều kiện:
1/ Từ Thứ 2->6: từ 8h30 đến 18h00 (tối đa là 9.5 tiếng)
2/ Thứ 7: từ 8h30 đến 12h00 (tối đa là 3.5 tiếng)
Không tính Chủ nhật, không cần loại trừ lễ tết, không cần loại trừ giờ nghỉ trưa.

Tạo công thức tại C3:

=(A3*B3>0)*SUM(TEXT(MOD(A3:B3,1)*24*TEXT(WEEKDAY(A3:B3,2),"["&{"<";"="}&"6]\1;\0")-8.5,"[<0]\0;[<"&{9.5;3.5}&"]0.00;\"&{"9\.5";"3\.5"})*{-1,1},(NETWORKDAYS.INTL(A3,B3,{"0000011";"1111101"})-TEXT(WEEKDAY(B3,2),"["&{"<";"="}&"6]\1;\0"))*{9.5;3.5})

Kết thúc bằng Ctrl+Shift+Enter.

Tham khảo file đính kèm.

Thân

 

[VetMini]

Hàm khủng thiệt. Tôi tính dịch sang công thức 365 cho gọn nhưng nửa chừng mệt quá. Dạo này hơi lười

 

[LmoovoenX]

Cho:
A3= "2024-01-01 07:40:12"
B3= "2024-01-05 09:55:09"

Với điều kiện:
1/ Từ Thứ 2->6: từ 8h30 đến 18h00 (tối đa là 9.5 tiếng)
2/ Thứ 7: từ 8h30 đến 12h00 (tối đa là 3.5 tiếng)
Không tính Chủ nhật, không cần loại trừ lễ tết, không cần loại trừ giờ nghỉ trưa.

Tạo công thức tại C3:

=(A3*B3>0)*SUM(TEXT(MOD(A3:B3,1)*24*TEXT(WEEKDAY(A3:B3,2),"["&{"<";"="}&"6]\1;\0")-8.5,"[<0]\0;[<"&{9.5;3.5}&"]0.00;\"&{"9\.5";"3\.5"})*{-1,1},(NETWORKDAYS.INTL(A3,B3,{"0000011";"1111101"})-TEXT(WEEKDAY(B3,2),"["&{"<";"="}&"6]\1;\0"))*{9.5;3.5})

Kết thúc bằng Ctrl+Shift+Enter.

Tham khảo file đính kèm.

Thân

Em nghĩ bài này chỉ có 3 trường hợp thì xài như này cho nhẹ anh ạ

=SUM(NETWORKDAYS.INTL(A4,B4,{1,"1111101"})*{9.5,3.5})-SUM(IF(WEEKDAY(A4:B4)=1,0,--TEXT((IF(WEEKDAY(A4:B4)=7,{8.5,12},{8.5,18})-MOD(A4:B4,1)*24)*{-1,1},"[<0]\0")))

Hoặc muốn tách ra nếu không xài công thức mảng

=NETWORKDAYS.INTL(A4,B4,1)*9.5+NETWORKDAYS.INTL(A4,B4,"1111101")*3.5-TEXT(MOD(A4,1)*24-IF(WEEKDAY(A4)=1,24,8.5),"[<0]\0")-IF(WEEKDAY(B4)=1,0,TEXT(IF(WEEKDAY(B4)=7,12,18)-MOD(B4,1)*24,"[<0]\0"))

 

[Phan Thế Hiệp]

Hàm khủng thiệt. Tôi tính dịch sang công thức 365 cho gọn nhưng nửa chừng mệt quá. Dạo này hơi lười

Dạ, đầu năm đầu tháng mà anh, tặng em nó "bao lì xì" to to, cho em nó mừng.

 

[Phan Thế Hiệp]

Em nghĩ bài này chỉ có 3 trường hợp thì xài như này cho nhẹ anh ạ

=SUM(NETWORKDAYS.INTL(A4,B4,{1,"1111101"})*{9.5,3.5})-SUM(IF(WEEKDAY(A4:B4)=1,0,--TEXT((IF(WEEKDAY(A4:B4)=7,{8.5,12},{8.5,18})-MOD(A4:B4,1)*24)*{-1,1},"[<0]\0")))

Hoặc muốn tách ra nếu không xài công thức mảng

=NETWORKDAYS.INTL(A4,B4,1)*9.5+NETWORKDAYS.INTL(A4,B4,"1111101")*3.5-TEXT(MOD(A4,1)*24-IF(WEEKDAY(A4)=1,24,8.5),"[<0]\0")-IF(WEEKDAY(B4)=1,0,TEXT(IF(WEEKDAY(B4)=7,12,18)-MOD(B4,1)*24,"[<0]\0"))

Không quan trọng việc công thức nặng hay nhẹ (lấy gì đo!?), cái vui nhất là khám phá giới hạn các điều kiện khác nhau trong TEXT, cái chưa ai thử qua mà được nếm lần đầu mới thú vị, thêm nữa là có cơ hội tạo và quản lý mảng đa chiều, và kết tổng chỉ sử dụng trong 1 SUM.

"Ngày đổi mới, người càng thêm đổi mới"
Ta hôm nay hơn được ta hôm qua bao nhiêu lần!?

Thân

 

[LmoovoenX]

Không quan trọng việc công thức nặng hay nhẹ (lấy gì đo!?), cái vui nhất là khám phá giới hạn các điều kiện khác nhau trong TEXT, cái chưa ai thử qua mà được nếm lần đầu mới thú vị, thêm nữa là có cơ hội tạo và quản lý mảng đa chiều, và kết tổng chỉ sử dụng trong 1 SUM.

Dạ, về dùng hàm text và cách tính thời gian, em học được của anh rất nhiều.
Cảm ơn anh và các anh chị em khác trong diễn đàn. Đầu năm mới chúc mọi người luôn khỏe mạnh.

 

[Phan Thế Hiệp]

Em nghĩ bài này chỉ có 3 trường hợp thì xài như này cho nhẹ anh ạ

=SUM(NETWORKDAYS.INTL(A4,B4,{1,"1111101"})*{9.5,3.5})-SUM(IF(WEEKDAY(A4:B4)=1,0,--TEXT((IF(WEEKDAY(A4:B4)=7,{8.5,12},{8.5,18})-MOD(A4:B4,1)*24)*{-1,1},"[<0]\0")))

Hoặc muốn tách ra nếu không xài công thức mảng

=NETWORKDAYS.INTL(A4,B4,1)*9.5+NETWORKDAYS.INTL(A4,B4,"1111101")*3.5-TEXT(MOD(A4,1)*24-IF(WEEKDAY(A4)=1,24,8.5),"[<0]\0")-IF(WEEKDAY(B4)=1,0,TEXT(IF(WEEKDAY(B4)=7,12,18)-MOD(B4,1)*24,"[<0]\0"))

Phân tích thì anh thấy có 1 chút xíu xiu cần suy nghĩ tí: khi dùng Mod(A4:B4,1), hoặc Mod(A4,1) và Mod(B4,1) vô hình chung khi trừ với các mốc giờ nó sẽ lấy giờ thực tế, mà đúng lý nó phải bị giới hạn lấy trong khung giờ (theo ngày thường hay ngày thứ 7), tức phải dùng Median('giờ thực tế', '{khung giờ}'). Dĩ nhiên, nếu giờ bắt đầu và kết thúc nằm trong mốc khung giờ thì quá tốt, nhưng khi đã thực hiện công thức nên đưa thêm giả thiết giờ thực tế nằm ngoài khung.

Ví dụ:
1/ Bđ: 29/12/2023 5:58:00 - Kt: 30/12/2023 7:00:00 tính tay là 9.5 tiếng

2/ Bđ: 29/12/2023 19:00:00 - Kt: 30/12/2023 11:45:00 tính tay là 3.25 tiếng

Vui là em đã nắm được tốt giải thuật bài toán tính giờ, và tùy biến nó theo mình hiểu. Rất đáng quý.

Thân

 

[LmoovoenX]

Phân tích thì anh thấy có 1 chút xíu xiu cần suy nghĩ tí: khi dùng Mod(A4:B4,1), hoặc Mod(A4,1) và Mod(B4,1) vô hình chung khi trừ với các mốc giờ nó sẽ lấy giờ thực tế, mà đúng lý nó phải bị giới hạn lấy trong khung giờ (theo ngày thường hay ngày thứ 7), tức phải dùng Median('giờ thực tế', '{khung giờ}'). Dĩ nhiên, nếu giờ bắt đầu và kết thúc nằm trong mốc khung giờ thì quá tốt, nhưng khi đã thực hiện công thức nên đưa thêm giả thiết giờ thực tế nằm ngoài khung.

Ví dụ:
1/ Bđ: 29/12/2023 5:58:00 - Kt: 30/12/2023 7:00:00 tính tay là 9.5 tiếng

2/ Bđ: 29/12/2023 19:00:00 - Kt: 30/12/2023 11:45:00 tính tay là 3.25 tiếng

Vui là em đã nắm được tốt giải thuật bài toán tính giờ, và tùy biến nó theo mình hiểu. Rất đáng quý.

Thân

Cảm ơn anh
Em có chỉnh sửa như sau, chủ yếu để giới hạn số giờ làm khi thời gian tính nằm ngoài mốc.

=SUM(NETWORKDAYS.INTL(A3,B3,{1,"1111101"})*{9.5,3.5})-SUM(IF(WEEKDAY(A3:B3)=1,0,--TEXT((IF(WEEKDAY(A3:B3)=7,{8.5,12},{8.5,18})-MOD(A3:B3,1)*24)*{-1,1},"[<0]\0;"&IF(WEEKDAY(A3:B3)=7,"[>3.5]3\.5","[>9.5]9\.5")&";0.00")))

Còn hàm ở dưới có tham khảo cách tính của anh để làm:

=SUM(TEXT(NETWORKDAYS.INTL(A3,B3-1,{1,"1111101"}),"[<0]\0")*{9.5,3.5})-SUM(IF(WEEKDAY(A3:B3)=1,0,TEXT(MOD(A3:B3,1)*24-8.5,"[<0]\0;"&IF(WEEKDAY(A3:B3)=7,"[>3.5]3\.5","[>9.5]9\.5")&";0.00")*{1,-1}))

 

[Phan Thế Hiệp]

Cảm ơn anh
Em có chỉnh sửa như sau, chủ yếu để giới hạn số giờ làm khi thời gian tính nằm ngoài mốc.

=SUM(NETWORKDAYS.INTL(A3,B3,{1,"1111101"})*{9.5,3.5})-SUM(IF(WEEKDAY(A3:B3)=1,0,--TEXT((IF(WEEKDAY(A3:B3)=7,{8.5,12},{8.5,18})-MOD(A3:B3,1)*24)*{-1,1},"[<0]\0;"&IF(WEEKDAY(A3:B3)=7,"[>3.5]3\.5","[>9.5]9\.5")&";0.00")))

Còn hàm ở dưới có tham khảo cách tính của anh để làm:

=SUM(TEXT(NETWORKDAYS.INTL(A3,B3-1,{1,"1111101"}),"[<0]\0")*{9.5,3.5})-SUM(IF(WEEKDAY(A3:B3)=1,0,TEXT(MOD(A3:B3,1)*24-8.5,"[<0]\0;"&IF(WEEKDAY(A3:B3)=7,"[>3.5]3\.5","[>9.5]9\.5")&";0.00")*{1,-1}))

Chính xác rồi đó!

Bài toán tính giờ lúc nào cũng thú vị như vậy đó.

Chúc em ngày vui

 

[Phan Thế Hiệp]

Cảm ơn anh
Em có chỉnh sửa như sau, chủ yếu để giới hạn số giờ làm khi thời gian tính nằm ngoài mốc.

=SUM(NETWORKDAYS.INTL(A3,B3,{1,"1111101"})*{9.5,3.5})-SUM(IF(WEEKDAY(A3:B3)=1,0,--TEXT((IF(WEEKDAY(A3:B3)=7,{8.5,12},{8.5,18})-MOD(A3:B3,1)*24)*{-1,1},"[<0]\0;"&IF(WEEKDAY(A3:B3)=7,"[>3.5]3\.5","[>9.5]9\.5")&";0.00")))

Còn hàm ở dưới có tham khảo cách tính của anh để làm:

=SUM(TEXT(NETWORKDAYS.INTL(A3,B3-1,{1,"1111101"}),"[<0]\0")*{9.5,3.5})-SUM(IF(WEEKDAY(A3:B3)=1,0,TEXT(MOD(A3:B3,1)*24-8.5,"[<0]\0;"&IF(WEEKDAY(A3:B3)=7,"[>3.5]3\.5","[>9.5]9\.5")&";0.00")*{1,-1}))

Thấy em sử dụng Text(Networkdays.intl()) rất hay, nên mượn em khúc đó và thêm 1 cách tìm ngày thường và thứ 7 tạo thành mảng 2 chiều như sau:

=SUM(TEXT(MOD(A3:B3,1)*24*(WEEKDAY(A3:B3,{2;1})*{1;-1}<{6;-6})-8.5,"[<0]\0;[<"&{9.5;3.5}&"]0.00;\"&{"9\.5";"3\.5"})*{-1,1},TEXT(NETWORKDAYS.INTL(A3,B3-1,{1,"1111101"}),"[<0]\0")*{9.5,3.5})

Cũng dùng 1 Sum cho 2 mảng: 2 chiều và 1 chiều.

Thân

 

[VetMini]

Giải thuật cho phiên bản 365 thử như vầy (tôi nói 365 bởi vì 365 có hàm Let và Lambda cho phép làm những con toán lặp lại nhiều lần)

- Nếu vào ra cùng ngày thì làm công thức căn bản

- Nếu vào ra khác ngày thì:
1. Lập một công thức tính số giờ ngày vào.
2. Lập một công thức tính số giờ ngày ra.
(cả hai đều có chiết tế cho ngày chủ nhật và thứ bảy)
3. Lập một công thức tính ngày vào +1 (n1) và ngày ra -1 (n2)
4. Công thức tính số giờ giữa hai ngày trên: (n2-n1+1 - (số ngày chủ nhật) - (số ngày thứ bảy)*6)*9.5

Để suy lại xem còn gì sót hôn ta.