Một vài ngày trước đây, một độc giả của tôi yêu cầu một công thức để trả về tất cả các ngày làm việc giữa hai ngày trong Google Sheets.
Anh ấy chỉ muốn loại bỏ các ngày cuối tuần và liệt kê tất cả các ngày làm việc giữa một ngày bắt đầu và một ngày kết thúc.
Tôi đã đi một bước xa hơn!
Ngoài việc tạo ra một danh sách các ngày như trên, tôi sẽ cho bạn biết cách loại bỏ các ngày nghỉ cụ thể (địa phương/quốc gia/quốc tế) khỏi danh sách.
Công thức để Liệt kê Tất cả các Ngày Làm việc Giữa Hai Ngày trong Google Sheets
Trong một trong những bài viết gần đây của tôi, [Làm thế nào để Liệt kê Các Ngày Tiếp Theo Loại trừ Cuối Tuần trong Google Sheets] (https://crawlan.com), tôi đã giải thích một phần của giải pháp cho vấn đề này.
Công thức đó trả về một số lượng cụ thể các ngày làm việc từ một ngày bắt đầu. Ngày kết thúc không phải là một phần của công thức đó.
Ở đó, tôi đã giải thích cách chúng ta có thể sử dụng hàm WORKDAY.INTL để làm điều đó.
Dưới đây là một tóm tắt.
Ngày bắt đầu sau đây ở ô C2, và ngày kết thúc ở ô C3.
Ngày bắt đầu: 15/12/2019
Ngày kết thúc: 02/01/2020
Tôi không xem xét ngày kết thúc trong thời gian này.
Công thức sau đây liệt kê năm ngày làm việc (n ngày) từ 16/12/2019 đến 20/12/2019.
=ArrayFormula(WORKDAY.INTL(C2,sequence(5,1),1))
Cách Chỉ định Ngày Kết thúc trong Công thức?
Công thức mảng trên sẽ trả lại năm ngày làm việc từ ngày bắt đầu. Số ngày làm việc được kiểm soát bởi sequence(5.
Điều đó có nghĩa là chúng ta có thể kiểm soát ngày kết thúc bằng cách sửa đổi phần Sequence của công thức trên.
Để biết cách công thức WORKDAY.INTL trên hoạt động, bạn có thể kiểm tra bài viết/hướng dẫn được đề cập ở trên.
Trong bài viết này, bạn có thể tìm hiểu cách trả về tất cả các ngày làm việc giữa hai ngày, tức là ngày bắt đầu và ngày kết thúc, trong Google Sheets.
Nghĩa là, làm thế nào để chỉ định một ngày kết thúc trong công thức trên?
Trước khi bắt đầu, bạn nên biết các ngày để loại trừ làm ngày cuối tuần khỏi danh sách các ngày làm việc giữa hai ngày trên.
Tôi đã sử dụng số cuối tuần 1 để loại trừ thứ Bảy và Chủ nhật trong công thức trên (xin xem phần đậm).
Xác định Tuần làm việc trong Công thức
Ở quốc gia của chúng tôi, tuần làm việc là năm ngày từ Thứ Hai đến Thứ Sáu. Số đại diện cho điều này (số cuối tuần) là 1.
Ở quốc gia của bạn (hoặc doanh nghiệp), tuần làm việc có thể là năm ngày hoặc ngay cả sáu ngày (làm việc từ Thứ Bảy đến Thứ Năm). Điều đó có nghĩa là ngày cuối tuần khác nhau.
Nếu vậy, bạn có thể tìm số cuối tuần tương ứng để sử dụng từ [đây] (https://infoinspired.com/google-docs/spreadsheet/google-sheets-date-functions-complete-guide/).
Trong bài viết đó, xin vui lòng nhảy đến chức năng NETWORKDAYS.INTL để lấy số cuối tuần mà bạn muốn. Tôi không lặp lại ở đây.
Điều đó rất quan trọng để trả về tất cả các ngày làm việc giữa hai ngày (một ngày bắt đầu và một ngày kết thúc) trong Google Sheets.
Chúng ta sẽ sử dụng ở đây cùng một chức năng, tức là NETWORKDAYS.INTL để bao gồm ngày kết thúc trong công thức trên.
Vai trò của NETWORKDAYS.INTL trong Sequence
Với hàm NETWORKDAYS.INTL, chúng ta có thể tìm hiểu sự khác biệt về ngày giữa hai ngày, không chỉ là sự khác biệt về ngày!
Để tính toán sự khác biệt về ngày, có các hàm khác, và chúng là DAYS và DATEDIF.
=DAYS(C3,C2)
=DATEDIF(C2,C3,"D")
=C3-C2Công thức trên sẽ trả về sự khác biệt về ngày giữa hai ngày trong ô C2 và C3, tức là 18.
Những gì chúng ta muốn là tổng số ngày làm việc giữa các ngày trong ô C2 và C3.
Chúng ta có thể tìm hiểu đó bằng cách sử dụng chức năng NETWORKDAYS.INTL trong Google Sheets.
=NETWORKDAYS.INTL(C2,C3,1)Công thức trên sẽ trả về 14.
Giải thích:
C2 – ngày bắt đầu
C3 – ngày kết thúc
,1) – số cuối tuần (Thứ Bảy và Chủ Nhật)
**Công thức để Liệt kê/Tạo tất cả các Ngày Làm việc giữa Hai Ngày trong Google Sheets:
Tôi sẽ sử dụng chức năng NETWORKDAYS.INTL trong phần sequence của công thức trên.
Thay đổi công thức này trả về 5 (hoặc bạn có thể nói ‘n’) ngày làm việc từ ngày bắt đầu (vui lòng tham khảo ảnh chụp màn hình ở trên);
=ArrayFormula(WORKDAY.INTL(C2,sequence(5,1),1))- thành một công thức trả về một số ngày làm việc nhất định từ một ngày bắt đầu.
Một số ngày làm việc nhất định có nghĩa là tổng số ngày làm việc giữa hai ngày (ngày bắt đầu và ngày kết thúc).
=ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1),1),1))
Lưu ý:
Sử dụng cùng một số cuối tuần trong WORKDAY.INTL cũng như trong các chức năng NETWORKDAYS.INTL.
UPDATE 1 vào ngày 20-10-2021:
Đôi khi công thức trên có thể trả về thêm 1-2 ngày làm việc vào cuối danh sách. Để sắp xếp điều đó, chúng ta có thể sử dụng chức năng QUERY.
=query(ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1),1),1)),"Select * where Col1 <date '"&TEXT(C3,"yyyy-mm-dd")&"'")Ngày Làm việc giữa Hai Ngày và Loại trừ Các Ngày Nghỉ cụ thể
Công thức trên đơn giản chỉ loại trừ các ngày cuối tuần được chỉ định khỏi các ngày được tạo ra.
Còn việc loại trừ các ngày lễ cụ thể (địa phương/quốc gia/quốc tế) ra sao?
Với một cột ngày nghỉ lễ bổ sung, chúng ta có thể quản lý điều đó.
Các bước:
Nhập các ngày nghỉ lễ dưới dạng danh sách và tham chiếu đến danh sách đó trong công thức.
Tôi đã nhập vào hai ngày nghỉ lễ trong ô D2 và D3 là 25/12/2019 (lễ Giáng sinh) và 01/01/2020 (Ngày của năm mới).
Làm thế nào để loại trừ hai ngày lễ này khỏi các ngày làm việc được tạo ra từ ngày 15/12/2019 đến 02/01/2020?
Nếu bạn kiểm tra cú pháp của các hàm WORKDAY.INTL và NETWORKDAYS.INTL, bạn có thể thấy rằng các hàm này có các đối số tùy chọn để loại trừ các ngày lễ.
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
WORKDAY.INTL(start_date, num_days, [weekend], [holidays])Xem cách tôi sử dụng các đối số tùy chọn đó trong công thức của mình bằng cách chỉ định D2:D5 là các ngày nghỉ lễ.
Công thức để Liệt kê tất cả các Ngày làm việc giữa Hai Ngày và Loại trừ Các Ngày Lễ được Chỉ định trong Google Sheets
=ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1,D2:D3),1),1,D2:D3))
CẬP NHẬT 2 vào ngày 30-05-2022:
Ở đây chúng ta cũng nên sửa công thức trên bằng cách sử dụng Truy vấn.
=query(ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1,D2:D3),1),1,D2:D3)),"Select * where Col1 <date '"&TEXT(C3,"yyyy-mm-dd")&"'")Chúng ta đã xong!
