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

bossh

Thành viên mới
Tham gia ngày
4 Tháng mười một 2007
Bài viết
31
Được thích
11
Điểm
665
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.

TG gian doan.png

Thank cả nhà nhiều.
 

File đính kèm

tiniluu123

Có bao giờ bạn tự hỏi "Tôi là ai?"
Tham gia ngày
25 Tháng một 2019
Bài viết
192
Được thích
100
Điểm
180
Nơi ở
Mỹ Tho- Tiền Giang
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.

View attachment 238259

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.
 

dazkangel

<New Horizons>
Tham gia ngày
28 Tháng hai 2017
Bài viết
2,847
Được thích
3,757
Điểm
360
Nơi ở
Đồng Nai
Làm thử,:
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)
 

ductdh94

Gà Mờ
Tham gia ngày
18 Tháng chín 2019
Bài viết
15
Được thích
18
Điểm
15
Tuổi
26
Nơi ở
Đà Nẵng
gửi bạn nhé. thay vì đi tìm thời gian gián đoạn, nó phải kéo dài trong 2 ngày từ (17h hôm trước đến 7h30 sáng hôm sau) gây khó khăn cho tính toán, mình tính luôn thời gian làm việc!
 

File đính kèm

bossh

Thành viên mới
Tham gia ngày
4 Tháng mười một 2007
Bài viết
31
Được thích
11
Điểm
665
Làm thử,:
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)
Làm thử mà đúng luôn, ghê thật :)
Bài đã được tự động gộp:

gửi bạn nhé. thay vì đi tìm thời gian gián đoạn, nó phải kéo dài trong 2 ngày từ (17h hôm trước đến 7h30 sáng hôm sau) gây khó khăn cho tính toán, mình tính luôn thời gian làm việc!
Quá tuyệt bạn ơi! Lúc đầu mình cũng nghĩ nên làm theo cách này nhưng bí logic nên đành phải tách cột. Giờ thì với công thức của bạn mình có được sự lựa chọn tốt thứ hai cùng với sự giúp đỡ của bạn dazkangel.

Thanks so much
 

tiniluu123

Có bao giờ bạn tự hỏi "Tôi là ai?"
Tham gia ngày
25 Tháng một 2019
Bài viết
192
Được thích
100
Điểm
180
Nơi ở
Mỹ Tho- Tiền Giang
Làm thử,:
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)
Mình ráp công thức vào thấy kết quả D11 đúng. D12, D13 thì sai á.
Bài đã được tự động gộp:

gửi bạn nhé. thay vì đi tìm thời gian gián đoạn, nó phải kéo dài trong 2 ngày từ (17h hôm trước đến 7h30 sáng hôm sau) gây khó khăn cho tính toán, mình tính luôn thời gian làm việc!
Bạn này làm đúng kết quả nè. Công nhận hay thiệt. Rãnh mình sẽ nghiên cứu công thức của bạn, dài thòn luôn. :D
Bài đã được tự động gộp:

Làm thử mà đúng luôn, ghê thật :)
Bài đã được tự động gộp:



Quá tuyệt bạn ơi! Lúc đầu mình cũng nghĩ nên làm theo cách này nhưng bí logic nên đành phải tách cột. Giờ thì với công thức của bạn mình có được sự lựa chọn tốt thứ hai cùng với sự giúp đỡ của bạn dazkangel.

Cảm ơn so much
Công thức của bạn Thiên thần bóng tối bị sai chỗ nào á.
Còn của bạn dh94 thì đúng.
Mình cũng bí logic, mà bí luôn giải thuật. Nghĩ không ra cách giải bài toán của bạn lun á, nhux đầu ghê, hi.
 
Lần chỉnh sửa cuối:

ductdh94

Gà Mờ
Tham gia ngày
18 Tháng chín 2019
Bài viết
15
Được thích
18
Điểm
15
Tuổi
26
Nơi ở
Đà Nẵng
Mình ráp công thức vào thấy kết quả D11 đúng. D12, D13 thì sai á.
Bài đã được tự động gộp:


Bạn này làm đúng kết quả nè. Công nhận hay thiệt. Rãnh mình sẽ nghiên cứu công thức của bạn, dài thòn luôn. :D
Bài đã được tự động gộp:



Công thức của bạn Thiên thần bóng tối bị sai chỗ nào á.
Còn của bạn dh94 thì đúng.
Mình cũng bí logic, mà bí luôn giải thuật. Nghĩ không ra cách giải bài toán của bạn lun á, nhux đầu ghê, hi.
Đây là một bài toán khá hay, file mình gửi bữa hôm trước chỉ tính đúng trong một vài trường hợp, còn lại sai nhiều. Đây là file sáng nay mình vừa làm , tuy nhiên đang viết trường hợp A>B, vẫn thiếu 1 trường hợp khi A<B (A, B là gì mọi người xem sheet Thuật toán nhé. Không biết trong này anh em có ý tưởng nào hay hơn cùng chia sẽ để rèn luyện tư duy ạ. Thanks
 

File đính kèm

Tham gia ngày
10 Tháng mười 2017
Bài viết
2,885
Được thích
8,537
Điểm
360
Nơi ở
Sài Gòn
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.

View attachment 238259

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

Thân
 

File đính kèm

bossh

Thành viên mới
Tham gia ngày
4 Tháng mười một 2007
Bài viết
31
Được thích
11
Điểm
665
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.
 

ductdh94

Gà Mờ
Tham gia ngày
18 Tháng chín 2019
Bài viết
15
Được thích
18
Điểm
15
Tuổi
26
Nơi ở
Đà Nẵng
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ử:
Mã:
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:

Thân
Cảm ơn bạn đã chia sẽ, các làm của bạn rất hay. Xem xong mới thấy tư duy của mình còn hạn chế rất nhiều.
 

bossh

Thành viên mới
Tham gia ngày
4 Tháng mười một 2007
Bài viết
31
Được thích
11
Điểm
665
Dear bạn Phan Thế Hiệp,

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?

Thanks,
Phạm Hùng
 
Tham gia ngày
10 Tháng mười 2017
Bài viết
2,885
Được thích
8,537
Điểm
360
Nơi ở
Sài Gòn
Dear bạn Phan Thế Hiệp,

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?

Cảm ơn,
Phạm Hùng
Xem giải thích mục '3.Thời gian không tính công lúc ra' theo link trên:

Thân
 

excel_lv1.5

Thành viên tiêu biểu
Tham gia ngày
20 Tháng mười 2017
Bài viết
679
Được thích
1,155
Điểm
360
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.

View attachment 238259

Thank cả nhà nhiều.
Có hàm Text với condition thì mấy bài thời gian có vẻ viết ngắn gọn lại được:
Mã:
E11=SUM(MMULT(TEXT(MOD(B11:C11,1)*24-{7.5;11.5;13},"[<0]\0;[<"&{4;0;4}&"];\"&{4;0;4})*60,{-1;1}))+(INT(C11)-INT(B11))*480
Bài đã được tự động gộp:
 
Lần chỉnh sửa cuối:
Tham gia ngày
10 Tháng mười 2017
Bài viết
2,885
Được thích
8,537
Điểm
360
Nơi ở
Sài Gòn
Có hàm Text với condition thì mấy bài thời gian có vẻ viết ngắn gọn lại được:
Mã:
E11=SUM(MMULT(TEXT(MOD(B11:C11,1)*24-{7.5;11.5;13},"[<0]\0;[<"&{4;0;4}&"];\"&{4;0;4})*60,{-1;1}))+(INT(C11)-INT(B11))*480
Bài đã được tự động gộp:
Vận dụng TEXT() quá hay!
Tư duy nhạy bén đến thán phục! Vẫn làm cho anh em kinh ngạc như ngày nào!

Khà khà khà
/-*+//-*+//-*+/
 
Tham gia ngày
10 Tháng mười 2017
Bài viết
2,885
Được thích
8,537
Điểm
360
Nơi ở
Sài Gòn
Có hàm Text với condition thì mấy bài thời gian có vẻ viết ngắn gọn lại được:
Mã:
E11=SUM(MMULT(TEXT(MOD(B11:C11,1)*24-{7.5;11.5;13},"[<0]\0;[<"&{4;0;4}&"];\"&{4;0;4})*60,{-1;1}))+(INT(C11)-INT(B11))*480
Bài đã được tự động gộp:
Nếu loại trừ ngày thứ 7, CN hoặc/và ngày nghỉ lễ thì cũng...hơi căng há!?
StartEnd
Friday 29/5/2020 10:39 AMSunday 31/5/2020 5:40 PM
Có hướng nào 'gọn gọn' không?

Thân
 

excel_lv1.5

Thành viên tiêu biểu
Tham gia ngày
20 Tháng mười 2017
Bài viết
679
Được thích
1,155
Điểm
360
Nếu loại trừ ngày thứ 7, CN hoặc/và ngày nghỉ lễ thì cũng...hơi căng há!?
StartEnd
Friday 29/5/2020 10:39 AMSunday 31/5/2020 5:40 PM
Có hướng nào 'gọn gọn' không?

Thân
Thấy cũng bình thường mà , xét thứ 7,CN thì thêm weekday là được:
Mã:
=SUM(MMULT(TEXT(MOD(A1:B1,1)*(WEEKDAY(A1:B1,2)<6)*24-{7.5;11.5;13},"[<0]\0;[<"&{4;0;4}&"];\"&{4;0;4})*60,{-1;1}))+(NETWORKDAYS.INTL(A1,B1,1)-(WEEKDAY(B1,2)<6))*480
Còn xét ngày nghỉ có list thì thêm macth đếm ngày , trường hợp này thì không gọn được đâu.
 

huuthang_bd

Chuyên gia GPE
Tham gia ngày
10 Tháng chín 2008
Bài viết
7,755
Được thích
8,974
Điểm
860
Nơi ở
TP.HCM
Mã:
=SUM(FREQUENCY(MOD(ROW(OFFSET($A$1,MOD(B11,1)*1440,,(C11-B11)*1440+0.1)),1440),{7.5;11.5;13;17}*60)*{0;1;0;1;0})
Mọi người test hộ công thức này xem ra kết quả có bất thường không.
Mã:
=COUNTBLANK(OFFSET(Z1000,1,1,1%,1%))
 

n0thing1988

Mù VBA
Tham gia ngày
30 Tháng chín 2013
Bài viết
1,555
Được thích
1,137
Điểm
560
Mã:
=SUM(FREQUENCY(MOD(ROW(OFFSET($A$1,MOD(B11,1)*1440,,(C11-B11)*1440+0.1)),1440),{7.5;11.5;13;17}*60)*{0;1;0;1;0})
Mọi người test hộ công thức này xem ra kết quả có bất thường không.
Mã:
=COUNTBLANK(OFFSET(Z1000,1,1,1%,1%))
Kết quả = 4 anh à
 
Top Bottom