Bạn đã bao giờ muốn tìm ngày hoặc khoảng ngày từ một số tuần trong Google Sheets chưa? Đây chính là cách ngược lại của hàm WEEKNUM.
Với sự trợ giúp của hàm WEEKNUM, bạn có thể lấy được số tuần từ một ngày cụ thể.
Kiểm tra ngay: Hướng dẫn các hàm ngày tháng cuối cùng trong Google Sheets.
Hiện tại không có hàm nào để lấy ngày hoặc khoảng ngày từ số tuần. Nhưng mình có một số công thức tùy chỉnh để làm điều này.
Những gì bạn cần làm là cung cấp một năm và một số tuần trong hai ô.
Công thức này có thể chuyển đổi số tuần đã cung cấp thành một ngày hoặc khoảng ngày rơi vào tuần đã cho.
Xem ảnh chụp màn hình ở trên cho năm trong ô A2 và số tuần trong ô B2.
Trong ô D2, mình có một công thức trả về ngày bắt đầu tuần, và trong ô E2, một công thức khác cho ngày kết thúc tuần.
Số tuần 7 năm 2018 bắt đầu vào ngày 11/02/2018 (Chủ Nhật) và kết thúc vào ngày 17/02/2018 (Thứ Bảy).
Trong ô G2, mình có thêm một công thức tự động điền ngày trong số tuần đã cho. Bạn có thể tìm thấy các công thức đó và mẹo sử dụng dưới đây.
Cách Tìm Ngày hoặc Khoảng Ngày từ Số Tuần trong Google Sheets
Đây là công thức mà mình đã nói ở trên trong ô D2, nó có thể giúp bạn lấy ngày bắt đầu từ một số tuần đã cho.
Công thức 1:
=MAX(DATE(A2,1,1),DATE(A2,1,1)-(WEEKDAY(DATE(A2,1,1))-1)+(B2-1)*7)
Dưới đây là công thức thứ hai trong ô E2 mà bạn có thể sử dụng để tìm ngày từ số tuần, nhưng cuối cùng trong khoảng thời gian của số tuần.
Công thức 2:
=MIN(DATE(A2,12,31),DATE(A2,1,1)-(WEEKDAY(DATE(A2,1,1))-1)+(B2-1)*7+6)
Cả hai công thức trên (phần in đậm) gần giống nhau (mình sẽ giải thích phần MAX/MIN sau).
Để lấy ngày kết thúc, thêm #6, tức là bằng 6 ngày, vào kết quả của công thức thứ nhất. Đó là công thức thứ hai của mình.
Một công thức nữa nằm ở ô G2. Đó là một công thức mảng. Trước khi đi vào đó, đây là logic của công thức trên.
Có một số bạn có thể muốn tìm hiểu cách công thức trên hoạt động.
Logic Của Công thức
Chúng ta có năm trong ô A1.
Với năm đó, chúng ta có thể lấy được ngày đầu tiên trong năm đó bằng công thức dưới đây. Điều này là điều đầu tiên bạn nên làm.
=DATE(A2,1,1)
Công thức này sẽ trả về ngày 01/01/2018 vì năm là 2018 trong ô A1.
Sau đó, mình có số tuần trong B1, là số 7.
Một số bạn có thể nghĩ rằng một công thức như dưới đây có thể tìm ra ngày từ số tuần trong Google Sheets.
=DATE(A2,1,1)+(7*7)
Nhưng có thể không hoạt động.
Trong công thức này, số 7 đầu tiên trong hệ số nhân đại diện cho số tuần thứ 7, và số 7 thứ hai đại diện cho số ngày trong một tuần.
Công thức này sẽ trả về ngày 19/02/2018, điều đó là sai! Tại sao?
Nó có thể trả về ngày cuối cùng trong tuần thứ 7, không phải ngày bắt đầu. Vậy còn công thức dưới đây?
=DATE(A2,1,1)+(6*7)
Nó gần như hoàn hảo. Nhưng vẫn chưa chính xác. Mình sẽ nói cho bạn biết tại sao.
Bạn có thể kiểm tra hướng dẫn về các hàm liên quan đến ngày của mình ở dưới đây, trong đó tôi đã đánh dấu số tuần trong tháng Giêng trên một ảnh chụp màn hình lịch.
Mẹo: Cách Tìm Số Tuần Trong Tháng Hiện Tại Trong Google Sheets
Điều đó là gì?
Thường thì một tuần đầy đủ từ Chủ Nhật đến Thứ Bảy.
Nhưng đối với tuần đầu tiên trong năm, có thể từ Chủ Nhật đến Thứ Bảy hoặc không phải.
Lưu ý: Để có từ Thứ Hai đến Chủ Nhật, hãy sử dụng đối số “type” trong hàm WEEKDAY(date, type) trong công thức của mình.
Tuần bắt đầu từ ngày thứ nhất của năm, bất kể Chủ Nhật, Thứ Hai, hoặc bất kỳ ngày nào khác.
Vì vậy, trong tuần đầu tiên, số ngày có thể là 7 hoặc không phải 7.
Điều này gây ra vấn đề trong các phép tính ở trên.
Xem Lịch năm 2018 này. Trong nó, chỉ có sáu ngày trong tuần đầu tiên.
Với sự trợ giúp của hàm WEEKDAY, chúng ta có thể tìm ra số đại diện cho ngày trong tuần của ngày đầu tiên trong năm đã cung cấp.
Công thức Weekday sẽ trả về số 1 cho Chủ Nhật, 2 cho Thứ Hai, và tiếp tục như vậy theo mặc định (vui lòng kiểm tra lưu ý của mình ở trên).
Bạn Cũng Có Thể Thích: Tải Về Mẫu Lịch Google Sheets Miễn Phí Và Cách Sử Dụng
Mình đã suy ra số đó trong công thức của mình và cộng thêm một để có một tuần đầy đủ bảy ngày ở đầu năm.
Nó có thể bao gồm một số ngày từ cuối năm ngoái. Phần MAX trong công thức giải quyết vấn đề đó (Hoạt động khi B2 là 1).
Phần MIN trong công thức thứ hai hoạt động khi B2 là 53 hoặc tuần cuối cùng trong năm.
Bây giờ chúng ta hãy xem cách điền vào một khoảng ngày từ số tuần trong Google Sheets.
Khoảng Ngày từ Số Tuần
Chủ đề của chúng ta là tìm ngày hoặc khoảng ngày từ số tuần trong Google Sheets.
Trong đó, mình đã giải thích cho bạn cách tìm ngày từ số tuần và năm. Dưới đây là phần khoảng ngày.
Dưới đây là công thức cho ô G2.
Công thức 3:
Công thức Chung: =ArrayFormula(row(indirect("A"&formula_1&":A"&formula_2)))
Với sự trợ giúp của các hàm ROW và INDIRECT, mình đã điền 7 ngày từ ngày bắt đầu của số tuần.
=ArrayFormula(row(indirect("A"&MAX(DATE(A2,1,1),DATE(A2,1,1)-(WEEKDAY(DATE(A2,1,1))-1)+(B2-1)*7)&":A"&MIN(DATE(A2,12,31),DATE(A2,1,1)-(WEEKDAY(DATE(A2,1,1))-1)+(B2-1)*7+6))))
Số ngày có thể ít hơn nếu số tuần là đầu hoặc cuối năm.
Việc tìm ngày hoặc khoảng ngày từ số tuần rất dễ dàng trong Google Sheets nếu bạn biết cách sử dụng các hàm ngày. Đó là tất cả. Hãy thử ngay!
Dịch thuật từ: Crawlan.com
