Tìm ngày giờ kết thúc dự kiến (1 người xem)

[Phan Thế Hiệp]

Do trong tuần ngồi không, nên lục lọi bài cũ, thấy bài này: Tính ngày giờ kết thúc công việc (không tính thời gian nghỉ) cũng hay hay, có thể nhiều anh em cần đến, nên đào lại cho những anh em nào yêu thích viết hàm và công thức giải trí, tạo công thức cho vui:

Điều kiện:
- Ngày làm việc: từ thứ 2 đến thứ 7; Chủ nhật và ngày lễ nghỉ việc
- Giờ làm việc 8 tiếng từ: 8h đến 17h (nghỉ giữa buổi từ 12h-13h không tính vào giờ làm)

Yêu cầu:

1. Ngày giờ bắt đầu: Cần điều chỉnh lại Ngày/Giờ (cột B file kèm), trả kết quả vào cột D "Ngày giờ bắt đầu (điều chỉnh)"
   • Nếu sớm hơn 8h00: chỉnh thành 8h00
   • Nếu rơi vào khung 12h -13h: chỉnh thành 13h
   • Nếu bằng hay quá 17h00: chỉnh thành 8h00 của ngày kế tiếp (không rơi vào ngày Chủ nhật và ngày lễ)
2. Ngày giờ kết thúc dự kiến: Dựa vào "Thời gian thực hiện (tiếng)" và "Ngày giờ bắt đầu (điều chỉnh)", tính toán và trả kết quả vào cột E "Ngày giờ kết thúc dự kiến "
   • Theo điều kiện như trên đã nêu
3. Chỉ dùng công thức, tùy ý dùng bất kỳ từ phiên bản 2010 trở đi; Không dùng cột phụ hay Names.
4. Kết quả mẫu tính tay như cột G và H

Bảng dữ liệu như file đính kèm.

Chúc anh em ngày vui

 

[Hoàng Tuấn 868]

Do trong tuần ngồi không, nên lục lọi bài cũ, thấy bài này: Tính ngày giờ kết thúc công việc (không tính thời gian nghỉ) cũng hay hay, có thể nhiều anh em cần đến, nên đào lại cho những anh em nào yêu thích viết hàm và công thức giải trí, tạo công thức cho vui:

Điều kiện:
- Ngày làm việc: từ thứ 2 đến thứ 7; Chủ nhật và ngày lễ nghỉ việc
- Giờ làm việc 8 tiếng từ: 8h đến 17h (nghỉ giữa buổi từ 12h-13h không tính vào giờ làm)

Yêu cầu:

1. Ngày giờ bắt đầu: Cần điều chỉnh lại Ngày/Giờ (cột B file kèm), trả kết quả vào cột D "Ngày giờ bắt đầu (điều chỉnh)"
   • Nếu sớm hơn 8h00: chỉnh thành 8h00
   • Nếu rơi vào khung 12h -13h: chỉnh thành 13h
   • Nếu quá 17h00: chỉnh thành 8h00 của ngày kế tiếp (không rơi vào ngày Chủ nhật và ngày lễ)
2. Ngày giờ kết thúc dự kiến: Dựa vào "Thời gian thực hiện (tiếng)" và "Ngày giờ bắt đầu", tính toán và trả kết quả vào cột E "Ngày giờ kết thúc dự kiến "
   • Theo điều kiện như trên đã nêu
3. Chỉ dùng công thức, tùy ý dùng bất kỳ từ phiên bản 2010 trở đi; Không dùng cột phụ hay Names.
4. Kết quả mẫu tính tay như cột G và H

Bảng dữ liệu như file đính kèm.

Chúc anh em ngày vui

Cảm ơn anh rất nhiều.

 

[Phan Thế Hiệp]

Cảm ơn anh rất nhiều.

Anh chưa có công thức mà cảm ơn rồi!

Khà khà

 

[Hoàng Tuấn 868]

Anh chưa có công thức mà cảm ơn rồi!

Khà khà

Em cảm ơn anh trước hết là vì chữ "Tâm" ở trong anh anh ạ.

 

[Phan Thế Hiệp]

Lỗi do tôi không rà xét lại khi dùng dữ liệu của người khác, các bạn chỉnh lại dữ liệu ô B6 là "T2. 10/03/2025 10:21". Lý do là ngày T6. 03/10/2025 đã có mặt trong danh mục các ngày lễ.


Cảm ơn các bạn

 

[vanthinh3101]

Lâu lâu mới được gặp lại người thầy, người anh đáng kính.
Em xin phép góp vui trước công thức cho Ngày giờ bắt đầu điều chỉnh:

D6=IF(OR(WEEKDAY(B6,2)=7,ISNUMBER(MATCH(INT(B6),$J$6:$J$10,0))),WORKDAY.INTL(B6,1,"0000001",$J$6:$J$10)+TIME(8,0,0),IF(MOD(B6,1)>TIME(17,0,0),WORKDAY.INTL(B6,1,"0000001",$J$6:$J$10)+TIME(8,0,0),IF(MOD(B6,1)>TIME(12,0,0),INT(B6)+MEDIAN(MOD(B6,1),TIME(13,0,0),TIME(17,0,0)),INT(B6)+MEDIAN(MOD(B6,1),TIME(8,0,0),TIME(12,0,0)))))
Fill xuống

Fill xuống.
Giải thích:
- Trường hợp 1: Ngày bắt đầu rơi vào ngày lễ hoặc Chủ nhật --> Dùng hàm Workday.Intl để tính ra ngày bắt đầu, cộng thêm 8h là thời gian làm việc buổi sáng.
- Trường hợp 2: Ngày bắt đầu rơi vào ngày làm việc
+ Quá 17h: Tương tự trường hợp 1
+ Quá 12h: Dùng Median để xác định trung vị giữa 3 mốc 13h, 17h và giờ của ngày bắt đầu
+ Còn lại thì Dùng Median để xác định trung vị giữa 3 mốc 8h, 12h và giờ của ngày bắt đầu

Việc tính thời điểm kết thúc để em nghĩ thêm
Chúc anh và mọi người 1 ngày vui.

 

[Krypton36GP]

Cảm ơn tác giả đã đưa ra một đề bài rất thú vị!
Mình xin góp vui bằng một file hoàn chỉnh, có chút không tự đúng lắm, rất vui nếu được anh chị góp ý thêm

Công thức tính ngày kết thúc, mình có tham khảo lại từ bomberman211 trong bài trước.
=WORKDAY.INTL(D6,ROUNDUP(MAX((C6-(17/24-TIME(HOUR(D6),MINUTE(D6),0)-IF(TIME(HOUR(D6),MINUTE(D6),0)<0.5,1/24,0))*24),0)/8,0),11,$J$6:$J$10)

 

[Phan Thế Hiệp]

Lâu lâu mới được gặp lại người thầy, người anh đáng kính.
Em xin phép góp vui trước công thức cho Ngày giờ bắt đầu điều chỉnh:

D6=IF(OR(WEEKDAY(B6,2)=7,ISNUMBER(MATCH(INT(B6),$J$6:$J$10,0))),WORKDAY.INTL(B6,1,"0000001",$J$6:$J$10)+TIME(8,0,0),IF(MOD(B6,1)>TIME(17,0,0),WORKDAY.INTL(B6,1,"0000001",$J$6:$J$10)+TIME(8,0,0),IF(MOD(B6,1)>TIME(12,0,0),INT(B6)+MEDIAN(MOD(B6,1),TIME(13,0,0),TIME(17,0,0)),INT(B6)+MEDIAN(MOD(B6,1),TIME(8,0,0),TIME(12,0,0)))))
Fill xuống

Fill xuống.
Giải thích:
- Trường hợp 1: Ngày bắt đầu rơi vào ngày lễ hoặc Chủ nhật --> Dùng hàm Workday.Intl để tính ra ngày bắt đầu, cộng thêm 8h là thời gian làm việc buổi sáng.
- Trường hợp 2: Ngày bắt đầu rơi vào ngày làm việc
+ Quá 17h: Tương tự trường hợp 1
+ Quá 12h: Dùng Median để xác định trung vị giữa 3 mốc 13h, 17h và giờ của ngày bắt đầu
+ Còn lại thì Dùng Median để xác định trung vị giữa 3 mốc 8h, 12h và giờ của ngày bắt đầu

Việc tính thời điểm kết thúc để em nghĩ thêm
Chúc anh và mọi người 1 ngày vui.

Giỏi em trai!
Lâu lắm mới thấy em tham gia, anh rất vui.

Cách phân định của em rõ ràng, lại xét thêm 2 trường hợp: ngày bắt đầu là ngày CN hay rơi vào ngày lễ ngoài yêu cầu của anh mà anh không để ý tới, rất hay!

Để công thức của em "bắt mắt", cho anh góp nhẹ vài sửa đổi nha!

1/ Vì điều kiện phần 1: OR( ngày CN, Ngày lễ), thì qua ngày kế (không phải CN, lễ) + 8h, vậy sẵn điều kiện phần 2 cũng +8h em nhét chung vào OR() đó luôn.
2/ Thay vì dùng "0000001" em thay bằng 11
3/ Các hàm TIME() có thể thay bằng 8/24 (=1/3), 13/24, 17/24...
4/ Dùng COUNTIF($J$6:$J$10,INT(B6)) thay cho ISNUMBER(MATCH(INT(B6),$J$6:$J$10,0)), vì Countif() nếu có/không nó sẽ ra 1/0 thì OR() cũng chấp nhận, còn Match() thì báo lỗi nếu không tìm thấy, nên phải dùng đến Isnumber().

Vậy công thức sẽ là: ô D6

=IF(OR(WEEKDAY(B6,2)=7,COUNTIF($J$6:$J$10,INT(B6)),MOD(B6,1)*24>17),WORKDAY.INTL(B6,1,11,$J$6:$J$10)+1/3,IF(MOD(B6,1)*24>12,INT(B6)+MEDIAN(MOD(B6,1)*24,13,17)/24,INT(B6)+MEDIAN(MOD(B6,1)*24,8,12)/24))

Xem vậy được không em?

Chúc điều tốt đẹp đến em và gia đình

 

[vanthinh3101]

Giỏi em trai!
Lâu lắm mới thấy em tham gia, anh rất vui.

Cách phân định của em rõ ràng, lại xét thêm 2 trường hợp: ngày bắt đầu là ngày CN hay rơi vào ngày lễ ngoài yêu cầu của anh mà anh không để ý tới, rất hay!

Để công thức của em "bắt mắt", cho anh góp nhẹ vài sửa đổi nha!

1/ Vì điều kiện phần 1: OR( ngày CN, Ngày lễ), thì qua ngày kế (không phải CN, lễ) + 8h, vậy sẵn điều kiện phần 2 cũng +8h em nhét chung vào OR() đó luôn.
2/ Thay vì dùng "0000001" em thay bằng 11
3/ Các hàm TIME() có thể thay bằng 8/24 (=1/3), 13/24, 17/24...
4/ Dùng COUNTIF($J$6:$J$10,INT(B6)) thay cho ISNUMBER(MATCH(INT(B6),$J$6:$J$10,0)), vì Countif() nếu có/không nó sẽ ra 1/0 thì OR() cũng chấp nhận, còn Match() thì báo lỗi nếu không tìm thấy, nên phải dùng đến Isnumber().

Vậy công thức sẽ là: ô D6

=IF(OR(WEEKDAY(B6,2)=7,COUNTIF($J$6:$J$10,INT(B6)),MOD(B6,1)*24>17),WORKDAY.INTL(B6,1,11,$J$6:$J$10)+1/3,IF(MOD(B6,1)*24>12,INT(B6)+MEDIAN(MOD(B6,1)*24,13,17)/24,INT(B6)+MEDIAN(MOD(B6,1)*24,8,12)/24))

Xem vậy được không em?

Chúc điều tốt đẹp đến em và gia đình

Quá tuyệt luôn anh ạ.
Thay vì dùng "0000001" em thay bằng 11 --> em thực tình là không nhớ các loại số này, chỉ nhớ đúng anh dạy là 0 là ngày làm việc, 1 là ngày nghỉ --> Cách viết "0000001" thực tế là 1 kiểu "lười ghi nhớ" anh ạ

 

[Phan Thế Hiệp]

Cảm ơn tác giả đã đưa ra một đề bài rất thú vị!
Mình xin góp vui bằng một file hoàn chỉnh, có chút không tự đúng lắm, rất vui nếu được anh chị góp ý thêm

Rất vui khi bạn tham gia

Dù công thức như thế nào, cũng đem lại cho bạn và tôi những giây phút trăn trở và hân hoan khi tìm ra được phương án giải quyết!

Việc học là muôn đời, nên khi có gì vui vui tôi hay mang lên "chia ngọt sẻ bùi" với anh em bè bạn. Bạn cứ yên tâm, sẽ có những đóng góp của nhiều anh em và qua đó bạn sẽ rút tỉa nhiều kiến thức cho mình.

Chúc bạn ngày vui

Thân

 

[vanthinh3101]

Rất vui khi bạn tham gia

Dù công thức như thế nào, cũng đem lại cho bạn và tôi những giây phút trăn trở và hân hoan khi tìm ra được phương án giải quyết!

Việc học là muôn đời, nên khi có gì vui vui tôi hay mang lên "chia ngọt sẻ bùi" với anh em bè bạn. Bạn cứ yên tâm, sẽ có những đóng góp của nhiều anh em và qua đó bạn sẽ rút tỉa nhiều kiến thức cho mình.

Chúc bạn ngày vui

Thân

Em nghĩ mãi vẫn không ra nổi cách tính Ngày giờ kết thúc dự kiến.
Nhờ anh có thể cho gợi ý về thuật toán để tư duy với ạ

 

[Phan Thế Hiệp]

Em nghĩ mãi vẫn không ra nổi cách tính Ngày giờ kết thúc dự kiến.
Nhờ anh có thể cho gợi ý về thuật toán để tư duy với ạ

Theo chỗ anh nghĩ được để giải quyết bài toán này có 2 thuật toán, nhưng do thời lượng hằng ngày làm việc là 8 tiếng nên cách 1 phù hợp và gọn gàng hơn, đó là:

"Thời gian thực hiện (tiếng)" loại trừ thời lượng của ngày bắt đầu, số còn lại nó sẽ định đoạt bao nhiêu ngày và giờ cần có.

Chúc em ngày vui

(Tb: anh ngủ đêêê... )

 

[huuthang_bd]

Do trong tuần ngồi không, nên lục lọi bài cũ, thấy bài này: Tính ngày giờ kết thúc công việc (không tính thời gian nghỉ) cũng hay hay, có thể nhiều anh em cần đến, nên đào lại cho những anh em nào yêu thích viết hàm và công thức giải trí, tạo công thức cho vui:

Điều kiện:
- Ngày làm việc: từ thứ 2 đến thứ 7; Chủ nhật và ngày lễ nghỉ việc
- Giờ làm việc 8 tiếng từ: 8h đến 17h (nghỉ giữa buổi từ 12h-13h không tính vào giờ làm)

Yêu cầu:

1. Ngày giờ bắt đầu: Cần điều chỉnh lại Ngày/Giờ (cột B file kèm), trả kết quả vào cột D "Ngày giờ bắt đầu (điều chỉnh)"
   • Nếu sớm hơn 8h00: chỉnh thành 8h00
   • Nếu rơi vào khung 12h -13h: chỉnh thành 13h
   • Nếu bằng hay quá 17h00: chỉnh thành 8h00 của ngày kế tiếp (không rơi vào ngày Chủ nhật và ngày lễ)
2. Ngày giờ kết thúc dự kiến: Dựa vào "Thời gian thực hiện (tiếng)" và "Ngày giờ bắt đầu (điều chỉnh)", tính toán và trả kết quả vào cột E "Ngày giờ kết thúc dự kiến "
   • Theo điều kiện như trên đã nêu
3. Chỉ dùng công thức, tùy ý dùng bất kỳ từ phiên bản 2010 trở đi; Không dùng cột phụ hay Names.
4. Kết quả mẫu tính tay như cột G và H

Bảng dữ liệu như file đính kèm.

Chúc anh em ngày vui

Góp vui bằng công thức tính ngày giờ kết thúc trực tiếp từ ngày giờ bắt đầu chưa điều chỉnh.

=LET(f;LAMBDA(a;x;WORKDAY.INTL(a-1;x+1;11;$J$6:$J$10));h;C6+IF(f(B6;0)=INT(B6);LET(m;MOD(B6;1)*24;MEDIAN(m;8;12)-8+MEDIAN(m;13;17)-13);0);t;MOD(h;8);f(B6;INT(h/8))+
(8+t+(t>4)-(t=0)*15)/24)

 

[Phan Thế Hiệp]

Góp vui bằng công thức tính ngày giờ kết thúc trực tiếp từ ngày giờ bắt đầu chưa điều chỉnh.

=LET(f;LAMBDA(a;x;WORKDAY.INTL(a-1;x+1;11;$J$6:$J$10));h;C6+IF(f(B6;0)=INT(B6);LET(m;MOD(B6;1)*24;MEDIAN(m;8;12)-8+MEDIAN(m;13;17)-13);0);t;MOD(h;8);f(B6;INT(h/8))+
(8+t+(t>4)-(t=0)*15)/24)

Đúng là "thứ dữ", chơi hai trong một luôn! Đáng nể thật!

Anh mà đăng bài thì ít nhất cũng học được điều gì đó mới mẻ, như công thức này chơi ngay Lambda() lên đầu, rồi gọi nó lúc nào muốn, không cần đưa vào Names, thật tiện lợi. Cảm ơn anh nhiều.

Chúc anh ngày thiệt vui

 

[Phan Thế Hiệp]

Các bạn thân mến

Đừng hoảng khi thấy anh @huuthang_bd đã ra "chiêu" lợi hại rồi chùn tâm không dám "múa rìu qua mắt thợ" .

Ảnh rất tốt, muốn chia sẻ công thức của mình gồm cả thuật toán và cách vận dụng hàm thông minh cho mọi người, tức là: "hổng giấu nghề" , thật sự trong lòng tôi luôn phục anh @huuthang_bd về việc này.

Đây chỉ là bài toán tôi chia sẻ có thể nhiều anh em phải gặp trong thực tế, mà sự vận dụng hàm/công thức để xử lý nó lại tùy thuộc vào khả năng của từng anh em có được, do vậy anh em cứ mạnh dạn nêu cách giải quyết của mình, mọi công thức anh em làm ra đều đáng trân trọng. Tôi biết trên diễn đàn này còn nhiều anh em khác đã có bài giải, nhưng "bên trong đã muốn, bên ngoài còn e", các bạn cứ mạnh dạn nêu lên cách tự mình tìm ra, dần dần sẽ tạo niềm tự tin cho các bạn.

Thông qua sự chia sẻ này, có thể sẽ có sự góp ý thêm, hoặc chúng ta sẽ tự rút ra nhiều bài học hữu ích để tự mình nâng cấp hoặc tìm ra lối đi riêng của mình, từ đó sẽ giải quyết nhiều bài toán khác trong tương lai.

Chúc việc học excel của các bạn ngày càng vui và lý thú

 

[VetMini]

Đúng là "thứ dữ", chơi hai trong một luôn! Đáng nể thật!

Anh mà đăng bài thì ít nhất cũng học được điều gì đó mới mẻ, như công thức này chơi ngay Lambda() lên đầu, rồi gọi nó lúc nào muốn, không cần đưa vào Names, thật tiện lợi. Cảm ơn anh nhiều.

Chúc anh ngày thiệt vui

Bạn phê như thế sợ nhiều người hiểu lầm.
Lambda nằm trong names có công dụng giống như hàm UDF viết bằng VBA.
Lambda khai báo và dùng tại chỗ giống như hàm/sub đã định là Private trong một Module VBA nào đó.
Cái khác nhau chính thức là không gian định danh. Người viết đặt nó trong names là vì họ thiết kế nó khá tổng quát, có thể dùng ở nhiều nơi khác nhau. Khi gói riêng (trong hàm Let) là người viết cho rằng nó quá đặc thù và không muốn nơi khác gọi nó.

Trong đề bài (bài #1), bạn không cho dùng cột phụ và names là vì bạn muốn các con tính phụ chỉ có mục đích phục vụ đề bài, không dùng ở nơi khác (*1). Và bài #13 đáp ứng đúng nhu cầu ấy.

Giải thích (*1):
Nếu không có điều kiện 3 ở bài #1, và người ta thêm cột phụ thì kết quả cột phụ này có thể được truy cập bất cứ nơi nào trong bảng tính.

 

[Phan Thế Hiệp]

Lambda nằm trong names có công dụng giống như hàm UDF viết bằng VBA.
Lambda khai báo và dùng tại chỗ giống như hàm/sub đã định là Private trong một Module VBA nào đó.

Dạ, cảm ơn anh đã hướng dẫn thêm.

Hổm rày thư thư, em vọc mấy cái hàm 365 để cho nó thấm từ từ như lời anh "vẽ đường cho nai chạy" trước đây , cũng lần lần tỏ tường công dụng của vài hàm mà em quan tâm đến trước. Không như trước đây có đủ thời gian và môi trường thuận lợi (đúng ngành nghề) để chuyên tâm nghiên cứu mổ xẻ nó, nay em học theo lối thực dụng, thấy có bài nào vui vui thì tự so sánh: trước đây dùng mảng thì xử lý làm sao, còn giờ thì 365 hỗ trợ hàm gì, có tiện lợi hơn không, và vì sao nó chạy hoặc ngược lại..v.v. Nên đôi khi có vài chỗ ngắc ngứ cũng đành để đó, giống hôm bữa em có nói với anh do em xài excel trên "dế yêu", dù biết là lambda hữu ích cho việc "tóm đầu" các đoạn công thức cùng công năng, nhưng không có Names nên bó tay, bó chân chạy không 'trơn tru', nay thấy anh @huuthang_bd bày nó trong Let() như "gãi đúng chỗ ngứa", thích chí cười khà khà vậy mà anh.

Vài dòng tâm sự đến anh

Chúc anh ngày vui khỏe

 

[Phan Thế Hiệp]

Bây giờ là sáng sớm Chủ nhật bên đó, tuần này thì tôi hết ngồi không, vã lại thời gian mấy ngày nay chắc các bạn cũng đã nghiền ngẫm, trăn trở với nó rồi, nên cũng là lúc tôi cũng phải đưa ra cách xử lý của mình. Tôi sẽ dùng cách đại trà để ai cũng dễ nắm bắt vấn đề.

Các bạn cũng sẽ như tôi lúng túng trong việc làm sao xác định được số ngày cộng thêm mà không bị rơi vào ngày CN và lễ, thấy rắc rối nhưng không phải vậy, và giống như tôi gợi ý với bạn @vanthinh3101 ở bài #12

"Thời gian thực hiện (tiếng)" loại trừ thời lượng của ngày bắt đầu, số còn lại nó sẽ định đoạt bao nhiêu ngày và giờ cần có.

Vd: Ngày bắt đầu còn 1.5 tiếng, thời gian thực hiện cần 20 tiếng, vậy thời gian còn lại là = 20 - 1.5 = 18.5 tiếng. Vì 1 ngày làm việc cố định 8 tiếng, vậy cần 3 ngày cộng thêm (8*3=24 tiếng -> bội số của 8) để thực hiện kết thúc, trong đó 2 ngày đầu mất 16 tiếng, vậy giờ kết thúc là 2.5 tiếng. Ngày kết thúc bắt đầu từ lúc 8h00 + 2.5 tiếng = 10h30 là mốc giờ kết thúc.

Muốn tìm bội số của 8 thì tôi dùng hàm Ceiling( 'Thời gian còn lại' /8 ,1). Vậy, đặt bội số này vào hàm:
=WORKDAY.INTL( 'Ngày bắt đầu', 'bội số của 8', 11, 'Ngày CN/ lễ' ) thì tìm được ngày cần thêm để kết thúc công việc mà không rơi vào CN và ngày lễ.

Tôi thực hiện cả hai phiên bản trước và từ 365 để các bạn trước 365 có thể tham khảo:

1/ Công thức trước 365:
a. Ngày giờ điều chỉnh:

=WORKDAY.INTL(B6,N(MOD(B6,1)*24>=17),11,$J$6:$J$12)+IF(MOD(B6,1)*24<17,TEXT(MAX(MOD(B6,1)*24,8),"[<12];[>13];13"),8)/24

b.Ngày giờ kết thúc:

=WORKDAY.INTL(D6,CEILING((C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)))/8,1),11,$J$6:$J$12)+IF(MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8),8+MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8)+(MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8)>4),17)/24

2/ Công thức 365:
a. Ngày giờ điều chỉnh:

=LET(gio,MOD(B6,1)*24,WORKDAY.INTL(B6,N(gio>=17),11,$J$6:$J$12)+IF(gio<17,TEXT(MAX(gio,8),"[<12];[>13];13"),8)/24)

b.Ngày giờ kết thúc:

=LET(Gcl,LET(gio,ROUND(MOD(D6,1)*24,6),C6-16+gio-(gio>12)),Glt,CEILING(Gcl/8,1),Gdc,Gcl-Glt*8,WORKDAY.INTL(D6,Glt,11,$J$6:$J$12)+(16+Gdc+(8+Gdc>4))/24)

Các bạn xem đồ biểu, thuật toán tôi có nêu trong file kèm

Chúc các bạn ngày vui

 

[vanthinh3101]

Bây giờ là sáng sớm Chủ nhật bên đó, tuần này thì tôi hết ngồi không, vã lại thời gian mấy ngày nay chắc các bạn cũng đã nghiền ngẫm, trăn trở với nó rồi, nên cũng là lúc tôi cũng phải đưa ra cách xử lý của mình. Tôi sẽ dùng cách đại trà để ai cũng dễ nắm bắt vấn đề.

Các bạn cũng sẽ như tôi lúng túng trong việc làm sao xác định được số ngày cộng thêm mà không bị rơi vào ngày CN và lễ, thấy rắc rối nhưng không phải vậy, và giống như tôi gợi ý với bạn @vanthinh3101 ở bài #12

Vd: Ngày bắt đầu còn 1.5 tiếng, thời gian thực hiện cần 20 tiếng, vậy thời gian còn lại là = 20 - 1.5 = 18.5 tiếng. Vì 1 ngày làm việc cố định 8 tiếng, vậy cần 3 ngày cộng thêm (8*3=24 tiếng -> bội số của 8) để thực hiện kết thúc, trong đó 2 ngày đầu mất 16 tiếng, vậy giờ kết thúc là 2.5 tiếng. Ngày kết thúc bắt đầu từ lúc 8h00 + 2.5 tiếng = 10h30 là mốc giờ kết thúc.

Muốn tìm bội số của 8 thì tôi dùng hàm Ceiling( 'Thời gian còn lại' /8 ,1). Vậy, đặt bội số này vào hàm:
=WORKDAY.INTL( 'Ngày bắt đầu', 'bội số của 8', 11, 'Ngày CN/ lễ' ) thì tìm được ngày cần thêm để kết thúc công việc mà không rơi vào CN và ngày lễ.

Tôi thực hiện cả hai phiên bản trước và từ 365 để các bạn trước 365 có thể tham khảo:

1/ Công thức trước 365:
a. Ngày giờ điều chỉnh:

=WORKDAY.INTL(B6,N(MOD(B6,1)*24>=17),11,$J$6:$J$12)+IF(MOD(B6,1)*24<17,TEXT(MAX(MOD(B6,1)*24,8),"[<12];[>13];13"),8)/24

b.Ngày giờ kết thúc:

=WORKDAY.INTL(D6,CEILING((C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)))/8,1),11,$J$6:$J$12)+IF(MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8),8+MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8)+(MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8)>4),17)/24

2/ Công thức 365:
a. Ngày giờ điều chỉnh:

=LET(gio,MOD(B6,1)*24,WORKDAY.INTL(B6,N(gio>=17),11,$J$6:$J$12)+IF(gio<17,TEXT(MAX(gio,8),"[<12];[>13];13"),8)/24)

b.Ngày giờ kết thúc:

=LET(Gcl,LET(gio,ROUND(MOD(D6,1)*24,6),C6-16+gio-(gio>12)),Glt,CEILING(Gcl/8,1),Gdc,Gcl-Glt*8,WORKDAY.INTL(D6,Glt,11,$J$6:$J$12)+(16+Gdc+(8+Gdc>4))/24)

Các bạn xem đồ biểu, thuật toán tôi có nêu trong file kèm

Chúc các bạn ngày vui

Bây giờ 2 ngày cuối tuần của em là luôn luôn phục vụ 2 bình rượu mơ anh ạ, hầu như không động vào máy tính.
Nay mới có thời gian để vào lại.
Thực sự là tư duy thuật toán của em vẫn còn rất hạn chế.
Em xin phép tiếp thu và nghiền ngẫm ạ.
Chúc anh 1 ngày vui vẻ

 

[Phan Thế Hiệp]

Bây giờ 2 ngày cuối tuần của em là luôn luôn phục vụ 2 bình rượu mơ anh ạ, hầu như không động vào máy tính.
Nay mới có thời gian để vào lại.
Thực sự là tư duy thuật toán của em vẫn còn rất hạn chế.
Em xin phép tiếp thu và nghiền ngẫm ạ.
Chúc anh 1 ngày vui vẻ

Em sướng hơn anh rồi! Em thì còn có 2 bình rượu mơ để uống, lại có 2 ngày phục vụ (anh hiểu theo nghĩa đen nha!), còn anh bắt đầu vào tuần uống rượu "mờ" cả mắt và toàn thân, khà khà

Không sao em, kiến thức thì thấm từ từ như mưa dầm thấm đất mới tốt. Mạnh dạn đánh giá đúng bản thân đang ở đâu và tiếp nhận thêm những cái mình chưa đạt, như chun nước không bao giờ đầy, thì việc học mới tấn tới, anh cũng vậy mà!

Chúc em tuần mới năng động và vui vẻ

 

[vanthinh3101]

Bây giờ là sáng sớm Chủ nhật bên đó, tuần này thì tôi hết ngồi không, vã lại thời gian mấy ngày nay chắc các bạn cũng đã nghiền ngẫm, trăn trở với nó rồi, nên cũng là lúc tôi cũng phải đưa ra cách xử lý của mình. Tôi sẽ dùng cách đại trà để ai cũng dễ nắm bắt vấn đề.

Các bạn cũng sẽ như tôi lúng túng trong việc làm sao xác định được số ngày cộng thêm mà không bị rơi vào ngày CN và lễ, thấy rắc rối nhưng không phải vậy, và giống như tôi gợi ý với bạn @vanthinh3101 ở bài #12

Vd: Ngày bắt đầu còn 1.5 tiếng, thời gian thực hiện cần 20 tiếng, vậy thời gian còn lại là = 20 - 1.5 = 18.5 tiếng. Vì 1 ngày làm việc cố định 8 tiếng, vậy cần 3 ngày cộng thêm (8*3=24 tiếng -> bội số của 8) để thực hiện kết thúc, trong đó 2 ngày đầu mất 16 tiếng, vậy giờ kết thúc là 2.5 tiếng. Ngày kết thúc bắt đầu từ lúc 8h00 + 2.5 tiếng = 10h30 là mốc giờ kết thúc.

Muốn tìm bội số của 8 thì tôi dùng hàm Ceiling( 'Thời gian còn lại' /8 ,1). Vậy, đặt bội số này vào hàm:
=WORKDAY.INTL( 'Ngày bắt đầu', 'bội số của 8', 11, 'Ngày CN/ lễ' ) thì tìm được ngày cần thêm để kết thúc công việc mà không rơi vào CN và ngày lễ.

Tôi thực hiện cả hai phiên bản trước và từ 365 để các bạn trước 365 có thể tham khảo:

1/ Công thức trước 365:
a. Ngày giờ điều chỉnh:

=WORKDAY.INTL(B6,N(MOD(B6,1)*24>=17),11,$J$6:$J$12)+IF(MOD(B6,1)*24<17,TEXT(MAX(MOD(B6,1)*24,8),"[<12];[>13];13"),8)/24

b.Ngày giờ kết thúc:

=WORKDAY.INTL(D6,CEILING((C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)))/8,1),11,$J$6:$J$12)+IF(MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8),8+MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8)+(MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8)>4),17)/24

2/ Công thức 365:
a. Ngày giờ điều chỉnh:

=LET(gio,MOD(B6,1)*24,WORKDAY.INTL(B6,N(gio>=17),11,$J$6:$J$12)+IF(gio<17,TEXT(MAX(gio,8),"[<12];[>13];13"),8)/24)

b.Ngày giờ kết thúc:

=LET(Gcl,LET(gio,ROUND(MOD(D6,1)*24,6),C6-16+gio-(gio>12)),Glt,CEILING(Gcl/8,1),Gdc,Gcl-Glt*8,WORKDAY.INTL(D6,Glt,11,$J$6:$J$12)+(16+Gdc+(8+Gdc>4))/24)

Các bạn xem đồ biểu, thuật toán tôi có nêu trong file kèm

Chúc các bạn ngày vui

Sau khi nghiền ngẫm, em đã tư duy ngược 1 chút anh ạ.
Nếu anh lấy 17h của Ngày bắt đầu làm mốc thì em sẽ tìm cách lấy 8h Ngày bắt đầu làm mốc.
Sau khi "vái tứ phương" bao gồm cả người và AI đã ra được 1 công thức mới:

E2=WORKDAY.INTL(D6,INT((MOD(D6,1)*24-8+C6)/8),11,$J$6:$J$10)+8/24+MOD(MOD(D6,1)*24-8+C6,8)/24+N(MOD(MOD(D6,1)*24-8+C6,8)>4)/24
Fill xuống

Em cũng học anh phân tích ra như sau:
- MOD(D6,1)*24-8+C6 --> Công thức tính ra số giờ đã sử dụng của Ngày bắt đầu và Thời hạn thực hiện.
- Sử dụng INT(.../8) --> tính ra phần nguyên để đưa vào tham số days trong Workday.Intl
- Workday.Intl(...) --> trả về kết quả là ngày làm việc tiếp theo, với mốc là 0h --> chính vì thế phải cộng thêm 8/24 để được thời gian bắt đầu là 8h
- MOD(....,8)/24 --> tính ra phần dư sau khi chia cho 8 để cộng thêm.
- N(MOD(...,8)>4)/24 --> phần dư mà lớn hơn 4h --> thời gian thực hiện sẽ vượt quá 12h -->phải cộng thêm 1h nghỉ trưa

Hại não thật anh ạ

 

[huuthang_bd]

MOD(D6,1)*24-8

Sai chỗ này nên đi cả bài.

 

[vanthinh3101]

Sai chỗ này nên đi cả bài.

Thật là thiếu sót, đúng là có trường hợp bị sai anh ạ.
Em nhờ anh chỉ giúp em để có hướng khắc phục ạ.
Em thì chỉ đang tư duy đơn giản là: Lấy phần giờ của ngày bắt đầu rồi nhân với 24 sẽ ra số giờ dạng số rồi trừ đi 8 (8h dạng số).

 

[huuthang_bd]

Thật là thiếu sót, đúng là có trường hợp bị sai anh ạ.
Em nhờ anh chỉ giúp em để có hướng khắc phục ạ.
Em thì chỉ đang tư duy đơn giản là: Lấy phần giờ của ngày bắt đầu rồi nhân với 24 sẽ ra số giờ dạng số rồi trừ đi 8 (8h dạng số).

Sai do bạn không trừ thời gian từ 12h00 đến 13h00 khi giờ bắt đầu lớn hơn 13h00.

 

[Phan Thế Hiệp]

Hại não thật anh ạ

Đáng khen em trai! Việc suy luận thì tốt cho não chứ có hại gì đâu em!

Như anh @huuthang_bd có chỉ điểm thì công thức của em sẽ là vầy:
=WORKDAY.INTL(D6,INT((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)/8),11,$J$6:$J$10)+(8+MOD(MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6,8)+(MOD(MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6,8)>4))/24

Tuy vậy, còn 1 trường hợp em chưa tính đến:

Vd: ô D7= T3 29/04/2025 15:30, giờ thực hiện cần: C7= 1.5 tiếng, nếu tính tay thì nó sẽ là T3 29/04/2025 17:00 kết thúc công việc, kết quả từ công thức của em là T6 02/05/2025 8:00. Anh thấy là do đoạn:
INT((MOD(D7,1)*24-8-(MOD(D7,1)*24>12)+C7)/8)

Em thử tính tay nha, sẽ biết lý do:
--> MOD(D7,1)*24-8-(MOD(D7,1)*24>12) = 15.5 - 8 - (15.5>12) = 6.5 tiếng
--> 6.5 + C7 = 6.5 + 1.5 = 8 tiếng
--> INT( 8 tiếng/8) = 1 (ngày)

Đem vào công thức WORKDAY.INTL(), lấy mốc ngày tại D7 tính tới, cộng thêm 1 ngày (không rơi vào CN, lễ), do vậy mình chưa kịp xét 8 tiếng này vẫn còn trong ngày hay qua ngày khác rồi nó cứ mặc nhiên tính thêm lên. Cũng giống vậy cho C7= 9.5 tiếng, 17.5 tiếng....

Anh thấy em ngày càng tiến bộ trong việc tự mình tìm hướng giải quyết vấn đề, thật lòng anh rất vui.

Chúc em ngày vui, bình an

(Tb: anh bên đạo nên chúc bình an cho nhau là việc thiện lành nhất)

 

[vanthinh3101]

Đáng khen em trai! Việc suy luận thì tốt cho não chứ có hại gì đâu em!

Như anh @huuthang_bd có chỉ điểm thì công thức của em sẽ là vầy:
=WORKDAY.INTL(D6,INT((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)/8),11,$J$6:$J$10)+(8+MOD(MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6,8)+(MOD(MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6,8)>4))/24

Tuy vậy, còn 1 trường hợp em chưa tính đến:

Vd: ô D7= T3 29/04/2025 15:30, giờ thực hiện cần: C7= 1.5 tiếng, nếu tính tay thì nó sẽ là T3 29/04/2025 17:00 kết thúc công việc, kết quả từ công thức của em là T6 02/05/2025 8:00. Anh thấy là do đoạn:
INT((MOD(D7,1)*24-8-(MOD(D7,1)*24>12)+C7)/8)

Em thử tính tay nha, sẽ biết lý do:
--> MOD(D7,1)*24-8-(MOD(D7,1)*24>12) = 15.5 - 8 - (15.5>12) = 6.5 tiếng
--> 6.5 + C7 = 6.5 + 1.5 = 8 tiếng
--> INT( 8 tiếng/8) = 1 (ngày)

Đem vào công thức WORKDAY.INTL(), lấy mốc ngày tại D7 tính tới, cộng thêm 1 ngày (không rơi vào CN, lễ), do vậy mình chưa kịp xét 8 tiếng này vẫn còn trong ngày hay qua ngày khác rồi nó cứ mặc nhiên tính thêm lên. Cũng giống vậy cho C7= 9.5 tiếng, 17.5 tiếng....

Anh thấy em ngày càng tiến bộ trong việc tự mình tìm hướng giải quyết vấn đề, thật lòng anh rất vui.

Chúc em ngày vui, bình an

(Tb: anh bên đạo nên chúc bình an cho nhau là việc thiện lành nhất)

Em cám ơn anh @Phan Thế Hiệp và anh @huuthang_bd nhiều ạ.
Vậy là công thức đã được hoàn chỉnh

 

[Phan Thế Hiệp]

Vậy là công thức đã được hoàn chỉnh

Chưa em ạ!

Em chỉ đọc phần trên bài anh viết, còn phần dưới từ chỗ "Tuy vậy,..." em không đọc , mà phần đó để xử lý còn phải thêm lắm bước "truân chuyên", khà khà

Chúc em ngày vui

 

[lehuong1409]

Chưa em ạ!

Em chỉ đọc phần trên bài anh viết, còn phần dưới từ chỗ "Tuy vậy,..." em không đọc , mà phần đó để xử lý còn phải thêm lắm bước "truân chuyên", khà khà

Chúc em ngày vui

Em chào anh ạ
Em thấy anh em bàn luận hăng say mà em thì nhìn công thức xong chắc lăng lẽ đi ra thôi ạ. Em vào để chào anh và cũng để ghim lại một bài viết tâm huyết để sau này em vào ngâm cứu sau ạ
Chúc anh một ngày mới tốt lành ạ

 

[Phan Thế Hiệp]

Em chào anh ạ
Em thấy anh em bàn luận hăng say mà em thì nhìn công thức xong chắc lăng lẽ đi ra thôi ạ. Em vào để chào anh và cũng để ghim lại một bài viết tâm huyết để sau này em vào ngâm cứu sau ạ
Chúc anh một ngày mới tốt lành ạ

Chào em,

Cũng đã lâu lắm rồi anh em mình mới trao đổi với nhau, em và gia đình vẫn vui khỏe.

Nguyên do anh đào lại bài này là do một người em trai thân thiết, anh rất quý, gửi cho anh một đề tài "hóc búa" cho anh cơ hội thư giản, thời may anh vẫn còn sức, gắng gượng vượt qua "tường lửa" đó. Tâm trạng vẫn còn say sưa vì bài hay quá, nên anh tìm bài tương tự nhưng dễ hơn nhiều lần để chia sẻ với anh em là bài này.

Lâu lâu nói chuyện với em anh rất vui.

Chúc em và gia đình ngày vui khỏe

 

[Phan Thế Hiệp]

Vậy là công thức đã được hoàn chỉnh

Như anh nói ở bài #, khi tham gia đóng góp bài sẽ nhận được sự góp ý thật lòng, đến khi nào sự hoàn thiện từ kết quả của công thức đem lại sẽ mang cho anh em mình 1 cảm giác vui lâng lâng khó lột tả. Làm việc này, lại gợi cho anh nhớ kỷ niệm cũ hồi còn là "ma mới", bước chân vào diễn đàn như "ếch ngồi đáy giếng" đã lâu, nay "lên được thành giếng" thấy cả 1 vùng trời rộng lớn mà bỡ ngỡ, toàn gặp "quái chiêu" khác lạ so với những gì sách vở ghi. Rồi bầu bạn đấu công thức với nhiều anh em: anh @HieuCD, @leoheocon... gặp "ma cũ" anh @huuthang_bd học toàn chiêu "độc", trong số đó có 1 em lúc nào cũng góp ý "công thức sai chỗ này", "sai chỗ kia" lắm lúc làm anh cáu bực trong lòng, nhưng ngẫm nghĩ lại "chính người chỉ cho mình chỗ sai mới là người bạn, người thầy chân chính", từ đó về sau anh cẩn thận hơn khi viết ra công thức, và anh rất ghét cái thói nói "làm đại" khi giải bài cho anh em. Kể từ đó anh rất quý người anh em chỉ chỗ mình sai, mà có lần anh gọi đùa là "Thanh tra CP", đó chính là @befaint.... Kể lể như vậy để mong em xem các góp ý dưới đây là tấm lòng của anh dành cho em nói riêng và anh em khác nói chung.

Các công thức anh dẫn giải sau là anh "chôm" y xì giải thuật của anh @huuthang_bd (anh chỉ có 'công' viết lại và dúi cho em xài, phải nói rõ vậy nha! )

Anh đã nhắc đoạn công thức:
INT((MOD(D7,1)*24-8-(MOD(D7,1)*24>12)+C7)/8)
làm chệch số ngày thực hiện cần có, lẽ ra nó phải trừ 8 (tiếng) thì mới đúng trong cùng ngày, nhưng nếu trừ như vậy sẽ ảnh hưởng đến những ngày khác không cùng ngày!?

Thay vì trực tiếp hàm INT(n/8), em dùng cách gián tiếp theo công thức:
INT(n/8)= (n - MOD(n,8))/8 (công thức này có hướng dẫn trong hàm Mod() của Microsoft)
 Gọi:

• gio=MOD(D7,1)*24
• gth=gio-8-(gio>12)+C7
• gcl=MOD(gth-1%,8)+1% (-1% để khi gth=8, thì Mod() không bằng 0, rồi +1% lại thành 8)
• Vậy (gth-gcl)/8 chính là INT(n/8) nói trên. Vd: (lấy lại vd bài #25) gio=15.5; gth= 15.5-8-(15.5>12)+1.5=8 ; gcl= Mod(8-1%,8)+1%=8 ==> (gth-gcl)/8= (8-8)/8=0 tức nó sẽ lấy cùng ngày khi đưa vào WORKDAY.INTL().
• Vd khác: giả sử giờ thực hiện là C7=2.5 tiếng, gio=15.5, gth= 15.5-8-(15.5>12)+2.5=9 ; gcl= Mod(9-1%,8)+1%=1 ==> (gth-gcl)/8= (9-1)/8=1 tức nó sẽ lấy thêm 1 ngày khi đưa vào WORKDAY.INTL().

Công thức sau cùng sẽ là:

=WORKDAY.INTL(D6,ROUND(((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)-(MOD((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)-1%,8)+1%))/8,0),11,$J$6:$J$10)+(8+MOD((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)-1%,8)+1%+(MOD((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)-1%,8)+1%>4))/24

Hoặc: (365)

=LET(gio,MOD(D6,1)*24,gth,gio-8-(gio>12)+C6,gcl,MOD(gth-1%,8)+1%,WORKDAY.INTL(D6,ROUND((gth-gcl)/8,0),11,$J$6:$J$12)+(8+gcl+(gcl>4))/24)

=LET(gio,ROUND(MOD(D6,1)*24,6),gth,gio-8-(gio>12)+C6,gcl,MOD(gth-1%,8)+1%,WORKDAY.INTL(D6-1,CEILING(gth/8,1),11,$J$6:$J$12)+(8+gcl+(gcl>4))/24)

Bây giờ công thức mới hoàn chỉnh

Chúc em ngày thiệt vui

 

[lehuong1409]

Chào em,

Cũng đã lâu lắm rồi anh em mình mới trao đổi với nhau, em và gia đình vẫn vui khỏe.

Nguyên do anh đào lại bài này là do một người em trai thân thiết, anh rất quý, gửi cho anh một đề tài "hóc búa" cho anh cơ hội thư giản, thời may anh vẫn còn sức, gắng gượng vượt qua "tường lửa" đó. Tâm trạng vẫn còn say sưa vì bài hay quá, nên anh tìm bài tương tự nhưng dễ hơn nhiều lần để chia sẻ với anh em là bài này.

Lâu lâu nói chuyện với em anh rất vui.

Chúc em và gia đình ngày vui khỏe

Thấy anh nhiệt tình với Excel khiến em cũng được tiếp thêm động lực học hỏi thêm nhưng rất lâu rồi em không học thêm mảng nên đối với em mấy đề tài này hơi khoai ạ. Lấy đà nghiên cứu dần một bài để lại cùng anh em hâm nóng lại diễn đàn như đợt đam mê công thức mảng mà em biết đến các anh ạ

 

[Phan Thế Hiệp]

Thấy anh nhiệt tình với Excel khiến em cũng được tiếp thêm động lực học hỏi thêm nhưng rất lâu rồi em không học thêm mảng nên đối với em mấy đề tài này hơi khoai ạ. Lấy đà nghiên cứu dần một bài để lại cùng anh em hâm nóng lại diễn đàn như đợt đam mê công thức mảng mà em biết đến các anh ạ

Việc học là muôn đời, nếu có thể là tác nhân làm cho em đam mê công thức mảng thì anh vui rồi.

Thấy ông 'Bill' không bóp cổ, mà ngược lại còn lắp thêm cánh cho mảng, làm cho các anh em yêu thích công thức mảng "thỏa chí tang bồng" bay nhảy.

Nếu em sử dụng được excel 365 trở đi thì nghiên cứu các hướng dẫn của anh @ptm0412 tại: Chủ đề 'Danh sách hàm mới của Excel 365 và bài viết hướng dẫn' https://www.giaiphapexcel.com/diend...i-của-excel-365-và-bài-viết-hướng-dẫn.165199/ có sẵn các Vd mẫu áp dụng.

Chúc em học vui

 

[lehuong1409]

Việc học là muôn đời, nếu có thể là tác nhân làm cho em đam mê công thức mảng thì anh vui rồi.

Thấy ông 'Bill' không bóp cổ, mà ngược lại còn lắp thêm cánh cho mảng, làm cho các anh em yêu thích công thức mảng "thỏa chí tang bồng" bay nhảy.

Nếu em sử dụng được excel 365 trở đi thì nghiên cứu các hướng dẫn của anh @ptm0412 tại: Chủ đề 'Danh sách hàm mới của Excel 365 và bài viết hướng dẫn' https://www.giaiphapexcel.com/diendan/threads/danh-sách-hàm-mới-của-excel-365-và-bài-viết-hướng-dẫn.165199/ có sẵn các Vd mẫu áp dụng.

Chúc em học vui

Các công thức của 365 em cũng dùng nhiều nhưng việc kết hợp nhiều lại với nhau với tư duy mảng rộng thì em vẫn còn nhiều hạn chế, nhờ thời gian mày mò công thức mảng chạy theo các tiền bối nên em tiếp cận với hàm của office 365 trộm vía cũng nhanh hơn anh ạ. Hi vọng thời gian tới em sẽ có thể tham gia bình luận nhiều hơn với các chủ đề của các "anh tài" ạ.

 

[vanthinh3101]

Như anh nói ở bài #, khi tham gia đóng góp bài sẽ nhận được sự góp ý thật lòng, đến khi nào sự hoàn thiện từ kết quả của công thức đem lại sẽ mang cho anh em mình 1 cảm giác vui lâng lâng khó lột tả. Làm việc này, lại gợi cho anh nhớ kỷ niệm cũ hồi còn là "ma mới", bước chân vào diễn đàn như "ếch ngồi đáy giếng" đã lâu, nay "lên được thành giếng" thấy cả 1 vùng trời rộng lớn mà bỡ ngỡ, toàn gặp "quái chiêu" khác lạ so với những gì sách vở ghi. Rồi bầu bạn đấu công thức với nhiều anh em: anh @HieuCD, @leoheocon... gặp "ma cũ" anh @huuthang_bd học toàn chiêu "độc", trong số đó có 1 em lúc nào cũng góp ý "công thức sai chỗ này", "sai chỗ kia" lắm lúc làm anh cáu bực trong lòng, nhưng ngẫm nghĩ lại "chính người chỉ cho mình chỗ sai mới là người bạn, người thầy chân chính", từ đó về sau anh cẩn thận hơn khi viết ra công thức, và anh rất ghét cái thói nói "làm đại" khi giải bài cho anh em. Kể từ đó anh rất quý người anh em chỉ chỗ mình sai, mà có lần anh gọi đùa là "Thanh tra CP", đó chính là @befaint.... Kể lể như vậy để mong em xem các góp ý dưới đây là tấm lòng của anh dành cho em nói riêng và anh em khác nói chung.

Các công thức anh dẫn giải sau là anh "chôm" y xì giải thuật của anh @huuthang_bd (anh chỉ có 'công' viết lại và dúi cho em xài, phải nói rõ vậy nha! )

Anh đã nhắc đoạn công thức:
INT((MOD(D7,1)*24-8-(MOD(D7,1)*24>12)+C7)/8)
làm chệch số ngày thực hiện cần có, lẽ ra nó phải trừ 8 (tiếng) thì mới đúng trong cùng ngày, nhưng nếu trừ như vậy sẽ ảnh hưởng đến những ngày khác không cùng ngày!?

Thay vì trực tiếp hàm INT(n/8), em dùng cách gián tiếp theo công thức:
INT(n/8)= (n - MOD(n,8))/8 (công thức này có hướng dẫn trong hàm Mod() của Microsoft)
 Gọi:

• gio=MOD(D7,1)*24
• gth=gio-8-(gio>12)+C7
• gcl=MOD(gth-1%,8)+1% (-1% để khi gth=8, thì Mod() không bằng 0, rồi +1% lại thành 8)
• Vậy (gth-gcl)/8 chính là INT(n/8) nói trên. Vd: (lấy lại vd bài #25) gio=15.5; gth= 15.5-8-(15.5>12)+1.5=8 ; gcl= Mod(8-1%,8)+1%=8 ==> (gth-gcl)/8= (8-8)/8=0 tức nó sẽ lấy cùng ngày khi đưa vào WORKDAY.INTL().
• Vd khác: giả sử giờ thực hiện là C7=2.5 tiếng, gio=15.5, gth= 15.5-8-(15.5>12)+2.5=9 ; gcl= Mod(9-1%,8)+1%=1 ==> (gth-gcl)/8= (9-1)/8=1 tức nó sẽ lấy thêm 1 ngày khi đưa vào WORKDAY.INTL().

Công thức sau cùng sẽ là:

=WORKDAY.INTL(D6,ROUND(((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)-(MOD((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)-1%,8)+1%))/8,0),11,$J$6:$J$10)+(8+MOD((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)-1%,8)+1%+(MOD((MOD(D6,1)*24-8-(MOD(D6,1)*24>12)+C6)-1%,8)+1%>4))/24

Hoặc: (365)

=LET(gio,MOD(D6,1)*24,gth,gio-8-(gio>12)+C6,gcl,MOD(gth-1%,8)+1%,WORKDAY.INTL(D6,ROUND((gth-gcl)/8,0),11,$J$6:$J$12)+(8+gcl+(gcl>4))/24)

=LET(gio,ROUND(MOD(D6,1)*24,6),gth,gio-8-(gio>12)+C6,gcl,MOD(gth-1%,8)+1%,WORKDAY.INTL(D6-1,CEILING(gth/8,1),11,$J$6:$J$12)+(8+gcl+(gcl>4))/24)

Bây giờ công thức mới hoàn chỉnh

Chúc em ngày thiệt vui

Em ngày đầu đến với diễn đàn thì mới biết qua loa mấy hàm IF, VLOOKUP, HLOOKUP, LEFT, RIGHT, thậm chí chưa rõ hàm INDEX, MATCH,...
Nhờ được mọi người trong diễn đàn tận tình chỉ bảo, giúp đỡ nên mới có được kiến thức kha khá, tự tin ứng dụng trong công việc và có thể giúp đỡ người khác.
Và vui hơn nữa khi gặp được những người gạo cội như các anh đã chỉ dẫn cách thức tư duy, cách thức học hỏi để có con đường học đúng và nhanh hơn.
Một lần nữa, em xin cảm ơn anh và tất cả mọi người.
Hi vọng diễn đàn sẽ tiếp tục có nhiều chủ đề hay, thú vị, thiết thực để giúp đỡ mọi người