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

Liên hệ QC

bossh

Thành viên mới
Tham gia
4/11/07
Bài viết
31
Được thích
13
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

  • Thoi gian gian doan.xlsx
    11.8 KB · Đọc: 50
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.
 
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)
 
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

  • Thoi gian gian doan.xlsx
    12 KB · Đọc: 33
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
 
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:
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

  • Thoi gian gian doan.xlsx
    16.1 KB · Đọc: 14
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

  • Thoi gian gian doan.xlsx
    11.8 KB · Đọc: 33
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.
 
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.
 
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
 
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
 
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:
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à
/-*+//-*+//-*+/
 
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
 
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.
 
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%))
 
Web KT
Back
Top Bottom