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 làm thế nào để 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” ở cuối bài viết 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 làm lịch trong Excel đơn giản bằng hàm

Như các bạn biết, một năm có 12 tháng, nhưng số ngày trong mỗi tháng không giống nhau. Vậy làm thế nào để tạo một bảng lịch có thể tự động thay đổi thứ, ngày, tuần khi chúng ta thay đổi tháng và năm? Cùng xem cách thực hiện đơn giản dưới đây nhé.

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 các thao tác để xác định thứ trong tuần theo hướng dẫn 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 toạ độ 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ó phải thứ 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 của ô C4 cho tất cả 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à:

• EOMONTH(DATE($C$1,$E$1,1),0): Tính toán ngày cuối cùng của tháng dựa trên số năm và tháng được chỉ định trong các ô C1 và E1.
• IF(H7+1>EOMONTH(DATE($C$1,$E$1,1),0),"",H7+1): Kiểm tra nếu giá trị của H7 tăng lên một vượt quá ngày cuối cùng của tháng, nếu có thì trả về giá trị trống (""), ngược lại trả về giá trị của H7 tăng lên một.

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))

Vậy là chúng ta đã thành công tạo bảng lịch trong Excel rồi đấy! Thật đơn giản phải không?

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

Kết luận

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. Hy vọng bạn sẽ thao tác đơn giản hơn khi chúng ta thành thạo các hàm.