Thủ thuật tạo lịch trong Excel bằng các hàm cực kỳ đơn giản

Bạn đã biết cách tạo lịch trong Excel cực đơn giản bằng các hàm thời gian chưa? Hãy xem ngay các bước làm chi tiết mà chúng mình hướng dẫn trong bài viết dưới đây nhé.

Trước hết, các bạn tải file dữ liệu mẫu mà chúng mình để trong mục “Tài liệu đính kèm” về để có thể thực hành ngay trong lúc đọc bài viết nhé. Hình ảnh dưới đây là kết quả mà các bạn thu được khi làm theo hướng dẫn của bài viết:

Bảng lịch trong Excel này có thể tự động thay đổi ngày tháng tương ứng theo tháng khi các bạn thay đổi phần tháng và phần năm ở dòng trên cùng nhé.

Cách tạo lịch trong Excel

Bước 1: Xác định thứ của ngày đầu tiên trong tháng

Chúng ta đều biết ngày đầu tiên của mỗi tháng luôn là ngày 1. Tuy nhiên chúng ta không biết ngày đầu tiên của tháng 3 trong ví dụ này sẽ là thứ mấy. Vì thế bạn cần thực hiện thao tác theo phân tích dưới đây.

Đầu tiên, các bạn dùng hàm DATE để xác định ngày đầu tiên của tháng theo công thức: =DATE($C$1,$E$1,1).

Lưu ý: Để có được ký hiệu $ nhằm cố định tọa độ trong công thức như của chúng mình thì các bạn bấm phím F4 sau khi nhập tham chiếu ô nhé.

Tiếp theo, chúng ta cần dùng hàm điều kiện để đặt quy ước là nếu thứ của ngày đầu tiên trong tháng này là thứ 2 thì mới đặt kết quả của hàm vào ô B4; nếu không phải thì trả về giá trị là rỗng. Chúng ta sẽ lồng cả hàm IF và hàm WEEKDAY vào công thức trên để được công thức hoàn chỉnh là:

=IF(WEEKDAY(DATE($C$1,$E$1,1))=2,DATE($C$1,$E$1,1),"")

Sau đó các bạn sẽ thu được kết quả là ô thứ 2 trống có nghĩa là ngày đầu tiên của tháng này không phải thứ 2:

Bây giờ chúng ta sẽ đặt thêm một điều kiện khác cho ô C4 (ô chứ ngày thứ 3 đầu tiên trong bảng) là: Nếu ngày trước đó khác rỗng thì chỉ cần cộng thêm 1 vào; nếu ngày trước đó đang rỗng thì lồng thêm công thức xác định ngày đầu tháng để xác định xem ngày đầu tháng có bằng 3 hay không. Sau đó nếu kết quả sai thì sẽ trả về là rỗng.

=IF(B4<>"",B4+1,IF(WEEKDAY(DATE($C$1,$E$1,1))=3,DATE($C$1,$E$1,1),""))

Kết quả thu về vẫn là một ô trống ở C4 vì ngày đầu tiên của tháng không phải thứ 3.

Sau đó các bạn sao chép công thức thì ô C4 sao hết các ngày từ thứ 4 đến chủ nhật. Tiếp theo, các bạn cần thay đổi giá trị WEEKDAY tương ứng cho từng ô theo quy ước dưới đây:

• Thứ 3 thì WEEKDAY bằng 3
• Thứ 4 thì WEEKDAY bằng 4
• Thứ 5 thì WEEKDAY bằng 5
• Thứ 6 thì WEEKDAY bằng 6
• Thứ 7 thì WEEKDAY bằng 7
• Riêng chủ nhật thì WEEKDAY bằng 1

Khi đã thay đổi xong giá trị WEEKDAY thì các bán sẽ thấy ngày 01/03/2020 đã hiện lên ở ô Chủ Nhật, đây là kết quả chính xác cuối cùng:

Bây giờ chúng mình sẽ thử thay đổi sang tháng khác để các bạn nhìn thấy rõ công dụng của các hàm được lồng vào nhau ở trên:

Tháng 4/2020

Tháng 4/2022

Xem thêm: Hàm IF lồng nhau và các thủ huật hay về hàm IF lồng nhau phần một

Bước 2: Xác định thứ của các ngày tiếp theo trong tháng

Từ tuần thứ 2 đến tuần thứ 4 thì việc xác định thứ của các ngày tiếp theo rất đơn là bằng ngày trước đó cộng thêm 1.

Ví dụ: Ngày thứ 2 thứ hai trong tháng được xác định bằng H4 + 1 với H4 là ô chứa dữ liệu liền trước nó.

Các bạn chỉ cần điền công thức cộng vào một ô rồi sao chép sang các ô còn lại cho đến hết tuần 4.

Xem thêm: Hướng dẫn sử dụng hàm DATE cơ bản trong Excel

Bước 3: Xác định thứ của ngày cuối cùng trong tháng

Sở dĩ ở bước trên chúng ta dừng lại ở tuần thứ 4 bởi vì tuy số ngày trong tháng khác nhau nhưng thường ngày cuối tháng sẽ rơi vào tuần thứ 5 hoặc thứ 6 của tháng đó. Do đó chúng ta cần sử dụng công thức cho tuần có chứa ngày cuối tháng để có thể trả về kết quả chính xác.

Công thức mà chúng mình sử dụng để điền vào ô B8 như sau:

=IF(H7+1>EOMONTH(DATE($C$1,$E$1,1),0),"",H7+1)

Ý nghĩa của công thức là:

• Nếu H7 + 1 mà lớn hơn ngày cuối tháng được xác định bằng hàm EOMONTH và hàm DATE lồng nhau thì tức là đã quá tháng nên lấy giá trị rỗng.
• Nếu H7 + 1 mà nhỏ hơn ngày cuối tháng được xác định bằng hàm EOMONTH và hàm DATE lồng nhau thì lấy thực hiện phép cộng thêm ngày như bình thường.

Tại ô C8 chúng ta có thể copy công thức ở ô B8 sang nhưng cần thay đổi một số tham chiếu ô. Cụ thể như sau:

=IF(B8="","",IF(B8+1>EOMONTH(DATE($C$1,$E$1,1),0),"",B8+1))

Ý nghĩa công thức này như sau:

• Nếu ô B8 là ô trống thì tức là ngày trước đó đã rỗng thì tất cả các ô phía sau cũng để là ô trống.
• Nếu ô B8 có giá trị thì tiếp tục thực hiện phép cộng như bình thường.

Sau đó các bạn sao chép công thức ở ô C8 sang các ô còn lại của tuần 5 là được nhé. Kết quả thu được tạm thời sẽ như sau:

Với tuần 6, chúng ta cũng dùng công thức như đã dùng cho tuần 5 nhưng thay đổi tham chiếu ô. Các bạn chỉ cần nhập công thức cho ô B9 và ô C9. Sau đó sao chép công thức từ ô C9 sang các ô còn lại là được. 

Công thức cụ thể cho ô B9 là:

=IF(H8="","",IF(H8+1>EOMONTH(DATE($C$1,$E$1,1),0),"",H8+1))

Công thức cụ thể cho ô C9 là:

=IF(B9="","",IF(B9+1>EOMONTH(DATE($C$1,$E$1,1),0),"",B9+1))

Như vậy là các bạn đã thực hiện thành công thao tác tạo lịch trong Excel và thu được kết quả có thể sử dụng cho nhiều năm, nhiều tháng khác nhau chỉ với các hàm đơn giản.

Xem thêm: Hướng dẫn chi tiết cách tạo lịch trong Excel có nút chọn ngày tháng

Kết luận

Qua bài viết này, các bạn có thể được các thao tác trong Excel sẽ trở nên đơn giản hơn khi chúng ta thành thạo các hàm.