Cách làm nổi bật N Ngày làm việc tiếp theo trong Google Sheets

GiGiKing·Tháng ba 25, 2024·0 comments

Khi tôi đầu tiên nghĩ ra ý tưởng viết một công thức để làm nổi bật N ngày làm việc tiếp theo trong Google Sheets, tôi nghĩ rằng đó là một điều dễ dàng bằng cách sử dụng một trong hai hàm ngày làm việc – WORKDAY hoặc WORKDAY.INTL.

Nhưng cuộc thử nghiệm của tôi đã trở nên khá thú vị. Theo thử nghiệm của tôi, nó yêu cầu thêm hai hàm nữa, đó là SEQUENCE hoặc ROW và REGEXMATCH.

Giữa SEQUENCE và ROW, tôi thích sử dụng SEQUENCE vì nó dễ đọc trong công thức.

Vì vậy, hướng dẫn này là kết quả của thử nghiệm đó. Hy vọng bạn sẽ thích và sử dụng nó trong công việc thực tế của bạn.

Giới thiệu

Để làm nổi bật N ngày làm việc tiếp theo, như tôi đã đề cập ở trên, chúng ta có thể dựa vào các hàm WORKDAY hoặc WORKDAY.INTL cùng với SEQUENCE và REGEXMATCH.

Bạn có thể thích: Cách sử dụng các hàm ngày tháng trong Google Sheets.

Nếu thứ 7 và Chủ nhật là ngày cuối tuần ở quốc gia của bạn, bạn có thể sử dụng hàm trước (WORKDAY).

Hàm sau (WORKDAY.INTL) giúp chúng ta xác định ngày cuối tuần “khác” (xem bảng ở cuối bài viết này). Như một ghi chú, ở một số quốc gia, thứ 6 và thứ 7 là ngày cuối tuần.

Tại sao làm nổi bật N ngày làm việc tiếp theo là một mẹo hữu ích?

Đôi khi chúng ta cam kết với người dùng/khách hàng rằng chúng ta sẽ đáp ứng yêu cầu trong 3, 7 hoặc 10 ngày làm việc tiếp theo hoặc tương tự.

Trong tình huống đó, nếu chúng ta có một danh sách chứa các ngày, chúng ta có thể làm nổi bật N ngày làm việc tiếp theo bằng cách sử dụng công thức kết hợp này trong định dạng có điều kiện.

Trong các ví dụ dưới đây, tôi sẽ làm nổi bật 10 ngày làm việc tiếp theo mà không tính ngày cuối tuần cũng như ngày lễ công cộng (được chỉ định theo tùy chỉnh).

Công thức để làm nổi bật N ngày làm việc tiếp theo trong Google Sheets

Ngày hôm nay theo hệ thống của tôi là 11-12-2020 (11 tháng 12 năm 2020). Vì vậy, định dạng có điều kiện trong ví dụ của tôi sẽ dựa trên ngày này.

Khi bạn áp dụng công thức trong bảng của bạn, nó sẽ điều chỉnh theo ngày hiện tại.

Định dạng có điều kiện cho 10 ngày làm việc tiếp theo loại trừ cuối tuần thứ 7 và Chủ nhật

Các ngày cho định dạng có điều kiện nằm trong A2:A20. Chỉ bỏ qua các giá trị trong phạm vi cột kề bên (B2:B20) vì nó giúp bạn hiểu các ngày cuối tuần và ngày làm việc.

Ở đây, tôi sẽ viết công thức bước qua bước. Vì vậy, bạn có thể nắm bắt nó nhanh chóng.

Bước 1: Trước tiên, chúng ta cần tạo ra các ngày cần làm nổi bật. Để làm điều đó, chúng ta có thể sử dụng công thức SEQUENCE.

Chỉ cần nhập công thức sau vào ô C2. Chúng ta sẽ sau đó cắt và dán công thức này vào trong quy tắc định dạng có điều kiện.

=ArrayFormula(
     WORKDAY(today(),sequence(10,1))
)

Công thức trên trả về các ngày cần làm nổi bật. Nếu bạn muốn làm nổi bật 3 ngày tiếp theo, thay thế số 10 bên trong SEQUENCE bằng 3.

Nếu bạn muốn tìm hiểu công thức này, hãy đọc bài viết này – Cách tạo ngày liên tục loại bỏ ngày cuối tuần trong Google Sheets.

Bước 2: Chúng ta phải khớp các ngày này trong phạm vi ngày trong A2:A20. Để làm điều đó, chúng ta có thể sử dụng công thức REGEXMATCH như sau.

=ArrayFormula(
     regexmatch(
        to_text($A$2:$A$20),
        textjoin("|",true,$C$2:$C$11)
     )
)

Hãy nhập công thức này vào ô D2.

Hãy tìm hiểu giải thích công thức ở đây – Cách khớp ngày dùng REGEXMATCH trong Google Sheets – Khớp đơn hoặc nhiều giá trị.

Bước 3: Để làm nổi bật các ngày làm việc tiếp theo 10 ngày loại trừ cuối tuần, bây giờ chúng ta cần làm nổi bật các ngày trong A2:A20, bất kể TRUE xuất hiện ở hàng tương ứng trong phạm vi D2:D10.

Hãy viết quy tắc định dạng có điều kiện đó bằng các công thức C2 và D2.

=ArrayFormula(
     regexmatch(
        to_text(A2),
        textjoin("|",true,
           ArrayFormula(WORKDAY(today(),sequence(10,1)))
        )
     )
)

Trong định dạng có điều kiện, chúng ta chỉ cần áp dụng quy tắc cho hàng đầu tiên trong phạm vi để làm nổi bật, đó là ô A2. Vì vậy, trong công thức trên, hãy thay thế $A$2:$A$20 bằng A2.

Xóa dấu đô trước các ô để có một tham chiếu tương đối. Điều này sẽ áp dụng quy tắc A2 cho toàn bộ phạm vi A2:A20.

Quy tắc định dạng có điều kiện để làm nổi bật 10 ngày làm việc tiếp theo loại trừ cuối tuần trong Google Sheets:

=ArrayFormula(
     regexmatch(
        to_text(A2),
        textjoin("|",true,
           ArrayFormula(WORKDAY(today(),sequence(10,1)))
        )
     )
)

Bước 4: Làm theo bốn bước sau đây.

  1. Chọn phạm vi A2:A20 và đi tới Format > Conditional Formatting.
  2. Trong bảng “Conditional format rules”, đảm bảo rằng “Apply to range” là A2:A20.
  3. Dưới ‘Format rules’, chọn ‘Custom formula is’ và cắt và dán công thức trên vào trường đã cho.
  4. Nhấp vào ‘Done’.

Làm cách nào để loại trừ các ngày lễ công cộng trong quá trình làm nổi bật?

Công thức trên chỉ loại trừ cuối tuần, nghĩa là thứ 7 và Chủ nhật, trong quá trình làm nổi bật. Tôi không muốn làm nổi bật ngày 25-12-2020 vì đó là ngày lễ công cộng do Giáng sinh năm 2020.

Chúng ta có thể chỉ định ngày lễ đó bên trong công thức #1 theo cú pháp dưới đây.

Cú pháp:

=ArrayFormula(
     WORKDAY(today(),sequence(10,1),{ngay_le_cong_1,ngay_le_cong_2,ngay_le_cong_3})
)

Vì chúng ta chỉ muốn loại trừ một ngày lễ công cộng, đó là ngày Giáng sinh, chúng ta có thể bao gồm 25-12-2020 như sau theo cú pháp trên.

=ArrayFormula(
     WORKDAY(today(),sequence(10,1),{date(2020,12,25)})
)

Khi chúng ta áp dụng thay đổi trong quy tắc định dạng có điều kiện hiện có, nó sẽ như sau.

Quy tắc công thức loại trừ ngày lễ công cộng:

=ArrayFormula(
     regexmatch(
        to_text(A2),
        textjoin("|",true,
           ArrayFormula(WORKDAY(today(),sequence(10,1),{date(2020,12,25)}))
        )
     )
)

Đó là tất cả. Chúc vui vẻ!

Nguồn:

GiGiKing

Related posts