Tính toán số phút trong giờ cao điểm, thấp điểm, bình thường giữa 2 mốc thời gian (1 người xem)

[ngquochuy.vlg]

Em có 2 cột ngày giờ bắt đầu và ngày giờ kết thúc, em muốn tính trong khoảng thời gian đó có bao nhiêu phút là giờ cao điểm, thấp điểm, bình thường?

Giờ thấp điểm: 00g00 --> 05g59
Giờ bình thường 1: 06g00 -->07g59
Giờ cao điểm: 08g00 --> 20g59
Giờ bình thường 2: 21g00 --> 23g59.

Có bác nào chỉ em công thức trong excel với. Cám ơn!
[TABLE="class: grid, width: 500"]
[TR]
[TD][TABLE="class: grid, width: 920"]
[TR]
[TD]TD[/TD]
[TD]0,5[/TD]
[TD="align: right"]00:00:00[/TD]
[TD="align: right"]05:59:59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BT1[/TD]
[TD]1[/TD]
[TD="align: right"]06:00:00[/TD]
[TD="align: right"]07:59:59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CD[/TD]
[TD]1,5[/TD]
[TD="align: right"]08:00:00[/TD]
[TD="align: right"]20:59:59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BT2[/TD]
[TD]1[/TD]
[TD="align: right"]21:00:00[/TD]
[TD="align: right"]23:59:59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]Ngày BĐ[/TD]
[TD="align: center"]Ngày KT[/TD]
[TD="align: center"]Giờ BĐ[/TD]
[TD="align: center"]Giờ KT[/TD]
[TD="align: center"]Thấp điểm[/TD]
[TD="align: center"]Bình thường[/TD]
[TD="align: center"]Cao điểm[/TD]
[TD="align: center"]Ghi chú[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]01/02/2017[/TD]
[TD]04:20:03 AM[/TD]
[TD]05:30:47 AM[/TD]
[TD]70[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]TD[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]01/02/2017[/TD]
[TD]04:20:03 AM[/TD]
[TD]06:30:47 AM[/TD]
[TD]130[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]TD + BT[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]01/02/2017[/TD]
[TD]04:20:03 AM[/TD]
[TD]10:30:47 AM[/TD]
[TD]370[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]TD + BT + CD[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]01/02/2017[/TD]
[TD]04:20:03 AM[/TD]
[TD]11:30:47 PM[/TD]
[TD]1.150[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]TD + BT + CD + BT[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]02/02/2017[/TD]
[TD]04:20:03 AM[/TD]
[TD]01:30:47 AM[/TD]
[TD]189[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]TD + BT+CD+BT + TD[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]01/02/2017[/TD]
[TD]06:20:03 AM[/TD]
[TD]07:30:47 AM[/TD]
[TD]70[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]BT1[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]01/02/2017[/TD]
[TD]06:20:03 AM[/TD]
[TD]09:30:47 AM[/TD]
[TD]190[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]BT1 + CD[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]01/02/2017[/TD]
[TD]06:20:03 AM[/TD]
[TD]11:30:47 PM[/TD]
[TD]1.030[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]BT1 + CD + BT2[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]02/02/2017[/TD]
[TD]06:20:03 AM[/TD]
[TD]01:30:47 AM[/TD]
[TD]#NUM![/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]BT1 + CD + BT2 + TD[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]02/02/2017[/TD]
[TD]06:20:03 AM[/TD]
[TD]06:10:47 PM[/TD]
[TD]1.070[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]BT1 + CD+BT2+TD + BT1[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]01/02/2017[/TD]
[TD]08:20:03 AM[/TD]
[TD]10:30:47 PM[/TD]
[TD]850[/TD]
[TD] [/TD]
[TD]759[/TD]
[TD]CD[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]01/02/2017[/TD]
[TD]08:20:03 AM[/TD]
[TD]11:30:47 PM[/TD]
[TD]910[/TD]
[TD] [/TD]
[TD]759[/TD]
[TD]CD + BT[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]02/02/2017[/TD]
[TD]08:20:03 AM[/TD]
[TD]01:30:47 AM[/TD]
[TD]#NUM![/TD]
[TD] [/TD]
[TD]1.539[/TD]
[TD]CD + BT + TD[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]02/02/2017[/TD]
[TD]08:20:03 AM[/TD]
[TD]07:10:47 AM[/TD]
[TD]#NUM![/TD]
[TD] [/TD]
[TD]1.539[/TD]
[TD]CD + BT + TD + BT[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]02/02/2017[/TD]
[TD]08:20:03 AM[/TD]
[TD]08:10:47 AM[/TD]
[TD]#NUM![/TD]
[TD] [/TD]
[TD]769[/TD]
[TD]CD + BT+TD+BT + CD[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]01/02/2017[/TD]
[TD]09:20:03 PM[/TD]
[TD]11:30:47 PM[/TD]
[TD]130[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]BT2[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]02/02/2017[/TD]
[TD]09:20:03 PM[/TD]
[TD]01:30:47 AM[/TD]
[TD]#NUM![/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]BT2 + TD[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]02/02/2017[/TD]
[TD]09:20:03 PM[/TD]
[TD]07:10:47 AM[/TD]
[TD]#NUM![/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]BT2 + TD + BT1[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]02/02/2017[/TD]
[TD]09:20:03 PM[/TD]
[TD]10:10:47 AM[/TD]
[TD]#NUM![/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]BT2 + TD + BT1 + CD[/TD]
[/TR]
[TR]
[TD]01/02/2017[/TD]
[TD]02/02/2017[/TD]
[TD]09:20:03 PM[/TD]
[TD]09:10:47 PM[/TD]
[TD]#NUM![/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]BT2 + TD+BT1+CD + BT2[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

 

[chucuoi92]

Em có 2 cột ngày giờ bắt đầu và ngày giờ kết thúc, em muốn tính trong khoảng thời gian đó có bao nhiêu phút là giờ cao điểm, thấp điểm, bình thường?

Giờ thấp điểm: 00g00 --> 05g59
Giờ bình thường 1: 06g00 -->07g59
Giờ cao điểm: 08g00 --> 20g59
Giờ bình thường 2: 21g00 --> 23g59.

Bạn kiểm tra lại file đính kèm xem có đúng không.

 

[quocgiacan]

Em có 2 cột ngày giờ bắt đầu và ngày giờ kết thúc, em muốn tính trong khoảng thời gian đó có bao nhiêu phút là giờ cao điểm, thấp điểm, bình thường?
Giờ thấp điểm: 00g00 --> 05g59
Giờ bình thường 1: 06g00 -->07g59
Giờ cao điểm: 08g00 --> 20g59
Giờ bình thường 2: 21g00 --> 23g59.

Chào ngquochuy.vlg,

Bạn thử:

E7=(($A7<$B7)*MIN($D7,$D$1)+(MAX(MIN($D7+($B7>$A7),$D$1),0)-$C7)*($C7<=$D$1))*24*60
F7=(($A7<$B7)*MAX(MIN($D7,$D$2)-$D$1,0)+(MAX(MIN($D7+($B7>$A7),$D$2)-$D$1,0)-MAX(MAX($D$1,$C7)-$D$1,0))*($C7<=$D$2))*24*60
G7=(($A7<$B7)*MAX(MIN($D7,$D$3)-$D$2,0)+(MAX(MIN($D7+($B7>$A7),$D$3)-$D$2,0)-MAX(MAX($D$2,$C7)-$D$2,0))*($C7<=$D$3))*24*60
H7=(($A7<$B7)*MAX(MIN($D7,$D$4)-$D$3,0)+(MAX(MIN($D7+($B7>$A7),$D$4)-$D$3,0)-MAX(MAX($D$3,$C7)-$D$3,0))*($C7<=$D$4))*24*60

Chọn từ E7:H7, rồi fill xuống.

Bạn xem file kèm đã đúng như cách tính tay của bạn chưa nha!

Chúc bạn ngày vui.

 

[ngquochuy.vlg]

Cám ơn bác quocgiacan!
Nhưng cho em hỏi thêm về phần ngày, em thay đổi ngày kết thúc thì số phút vẫn chưa tính thêm phần ngày. VD dòng 11, 16, 21, 26 em cố ý để tình huống thời gian bắt đầu và thời gian kết thúc trải qua nhiều ngày nhưng thời gian kết thúc < thời gian bắt đầu. Mong bác giúp nốt (bác tính thêm cột ngày bắt đầu và ngày kết thúc).

File của bác chucuoi92 thì giải quyết được thì em không biết được công thức tính toán. Nhưng vẫn cám ơn bác đã trả lời.

 

[chucuoi92]

File của bác chucuoi92 thì giải quyết được thì em không biết được công thức tính toán. Nhưng vẫn cám ơn bác đã trả lời.

Công thức tính toán nằm trong "Name".
Bạn nhấn Ctrl + F3 sẽ thấy các "Name" nằm trong đó

 

[quocgiacan]

Cám ơn bác quocgiacan!
Nhưng cho em hỏi thêm về phần ngày, em thay đổi ngày kết thúc thì số phút vẫn chưa tính thêm phần ngày. VD dòng 11, 16, 21, 26 em cố ý để tình huống thời gian bắt đầu và thời gian kết thúc trải qua nhiều ngày nhưng thời gian kết thúc < thời gian bắt đầu. Mong bác giúp nốt (bác tính thêm cột ngày bắt đầu và ngày kết thúc).

File của bác chucuoi92 thì giải quyết được thì em không biết được công thức tính toán. Nhưng vẫn cám ơn bác đã trả lời.

Úi chao!

Tôi tưởng bạn cao lắm tính cho 2 ngày liền kề, ai dè bạn cho nó ngày tùy ý thì công thức phải khác rồi.

Vậy nha!

Chúc bạn ngày vui.

 

[quocgiacan]

Cám ơn bác quocgiacan!
Nhưng cho em hỏi thêm về phần ngày, em thay đổi ngày kết thúc thì số phút vẫn chưa tính thêm phần ngày. VD dòng 11, 16, 21, 26 em cố ý để tình huống thời gian bắt đầu và thời gian kết thúc trải qua nhiều ngày nhưng thời gian kết thúc < thời gian bắt đầu. Mong bác giúp nốt (bác tính thêm cột ngày bắt đầu và ngày kết thúc).

File của bác chucuoi92 thì giải quyết được thì em không biết được công thức tính toán. Nhưng vẫn cám ơn bác đã trả lời.

Bạn thêm vào đầu mỗi đoạn:

Vd: Thấp điểm: MAX($B7-$A7-1,0)*($D$1-$C$1)+....(công thức cũ)

Tức tính thêm các ngày trọn giờ thấp điểm. Tương tự cho các giờ khác.
​

Bạn xem file kèm.

Chúc bạn ngày vui.

 

[eke_rula]

Em có 2 cột ngày giờ bắt đầu và ngày giờ kết thúc, em muốn tính trong khoảng thời gian đó có bao nhiêu phút là giờ cao điểm, thấp điểm, bình thường?

Giờ thấp điểm: 00g00 --> 05g59
Giờ bình thường 1: 06g00 -->07g59
Giờ cao điểm: 08g00 --> 20g59
Giờ bình thường 2: 21g00 --> 23g59.

Có bác nào chỉ em công thức trong excel với. Cám ơn!

Thêm cách khác dùng mảng để tính bài này, bạn chọn vùng E7:H7, nhập công thức này:

=ROUND(TRANSPOSE(MMULT($C$1:$D$4,{-1;1})*(B7-A7-1)+MMULT(IF($C$1:$D$4<=C7,C7,$C$1:$D$4)+IF($C$1:$D$4>=D7,D7,$C$1:$D$4),{-1;1}))*1440,2)

Bấm Ctrl +Shift +Enter rồi fill xuống!!!

 

[HieuCD]

dùng hàm if cho đơn giản, chỉnh lại bảng điều kiện cho chuẩn

 

[ngquochuy.vlg]

Cám ơn các bác!

Em đã thực hiện xong bảng tính, còn cách nào tối ưu em sẽ nghiên cứu kỹ hơn. Một lần nữa chân thành cám ơn các bác!